2

I am working on adding some stored procedures to my database and am stuck on how to do the SQL for what I want to do.

For the procedure I am working on right now, it needs to have two parameters, a table name, and a column name.

The procedure needs to check if that column exists within the table, and if it does, return all of the data within that column.

Now I have been going through both this site, and Google trying to figure out how I'd go about programming this, but needless to say I am utterly confused and not sure what to do.

If I were to pseudocode the procedure it would look like so:

CREATE PROCEDURE GetTableColumn (
    @ColName VARCHAR(25),
    @TblName TABLE READONLY
) AS
BEGIN
    IF (EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TblName AND COLUMN_NAME = @ColName))
        SELECT @ColName
        FROM @TblName
END

I have tried the above code, and it doesn't work, basically I have no idea how to set up a variable that can be a table.

3

You would need to use dynamic sql. You final select line should be:

declare @sql varchar(max)
set @sql = 'select ' + @colName + ' from ' + @TblName
exec(@sql)
1

You should be passing in both the column and and table as varchar(x) parameters. You are looking at the correct meta data views (INFORMATION.SCHEMA.COLUMNS). If you find that the table and column combination exist, you need to build a SQL statement and execute it dynamically.

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql?view=sql-server-2017

http://www.sqlteam.com/article/using-dynamic-sql-in-stored-procedures

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.