How to include a variable data into a SQL Statement

Question

I would like to know, how to include a variable as a part of a SQL statement, to clarify more about my question, here is the code

declare @PartNo as nvarchar(20)
declare @PPFno as nvarchar(20)
declare @Dimension as nvarchar(30)
declare @cursor CURSOR
declare @colname as nvarchar(30)
declare @top as integer
declare @query as nvarchar(MAX)
declare @categoryid NVARCHAR(MAX) 

set @cursor = CURSOR FOR 
    (select [Name] from sys.columns where object_id = (select object_id from sys.tables where name = 'ProductProperty') and [Name] like 'T%' and [name] <> 'TEMP')order by [Name] asc

    OPEN @cursor

    FETCH NEXT

    FROM @cursor INTO @colname
    WHILE @@FETCH_STATUS = 0
    BEGIN
        --set @query =  (select @colname from ProductProperty where PartNo = @PartNo  and PPFNo = @PPFno )

        --set @query = 'select distinct '+@colname+' from ProductProperty where PartNo = '''+@PartNo+'''  and PPFNo = '''+@PPFno+''' and DName = '''+@Dimension+''''

BEGIN

EXEC sp_executesql N'set @categoryid = (select distinct @colname from ProductProperty where PartNo = @PartNo  and PPFNo = @PPFno and DName = @Dimension)', 
N'@colname nvarchar(30), @PartNo nvarchar(20), @PPFno nvarchar(20),@Dimension nvarchar(30), @categoryid NVARCHAR(MAX) OUTPUT', @colname,@PartNo, @PPFno,@Dimension, @categoryid OUTPUT

select @categoryid,@colname ,@PartNo

END
    --end

    FETCH NEXT

    FROM @cursor INTO @colName
    END

    CLOSE @cursor
    DEALLOCATE @cursor

Please do take note I did not included the variable types. I would just want to know how can @colname become part of the SQL Statement.

To Elaborate more. Using this code, I am receiving this data

Where T1 is the table name. I want to create a query where I could pass table names into a variable, then retrieve the contents of that query.

So the SQL Query should look like this:

select T1 from ProductProperty

But I am not receiving the query, instead, I am receiving the variable data, which is in the screenshot above.

The problem is, if you might notice in my code, I have the variable @categoryid as a output parameter. This is to check the contents of the query.

it seems like I am producing a query which looks like this

select 'T1' from ProductProperty

May I ask, what am I doing wrong? If you would want additional information, please do tell me.

EDIT:

completed the query for more clarification

@PrashantPimpale Yep, its working. If I pass a static query, for example select T1 from table, with the parameters and all, I can retreive the data that I wanted. — Mr.J
– Mr.J, Commented Jul 18, 2018 at 8:05
I m not more familiar with Output param so if you can post expected data and available data then will help — Prashant Pimpale
– Prashant Pimpale, Commented Jul 18, 2018 at 8:07
select distinct ' + @colname + ' from... (watch out for SQL injection vulnerability though, depending on the source of @colname) — Diado
– Diado, Commented Jul 18, 2018 at 8:08
@Diado That would work if I will only pass 1 data, I also need to get the value of the query, check if its null or not, the process it again. — Mr.J
– Mr.J, Commented Jul 18, 2018 at 8:18

Diado · Accepted Answer · 2018-07-18 09:15:04Z

1

The issue here is that you are telling it to select the value of the @colname variable itself as the first value in your query, not the value of the column name stored in the variable. This is the equivalent of doing:

SELECT @colname;

What you need to do is output the value of the @colname variable into the SQL string you are passing to sp_executesql. As long as the @colname variable isn't entered by the user, you can concatenate it into the string passed to sp_executesql, as follows:

DECLARE @SqlQuery NVARCHAR(MAX);

SET @SqlQuery = N'set @categoryid = (select distinct ' + @colname + ' from ProductProperty 
where PartNo = @PartNo  and PPFNo = @PPFno and DName = @Dimension)';

EXEC sp_executesql @SqlQuery, 
@PartNo nvarchar(20), @PPFno nvarchar(20),@Dimension 
nvarchar(30), @categoryid NVARCHAR(MAX) OUTPUT', @PartNo, @PPFno,@Dimension, 
@categoryid OUTPUT

select @categoryid, @colname, @PartNo

That will effectively give you:

EXEC sp_executesql N'set @categoryid = (select distinct T1 from ProductProperty 
where PartNo = @PartNo  and PPFNo = @PPFno and DName = @Dimension)', 
@PartNo nvarchar(20), @PPFno nvarchar(20),@Dimension 
nvarchar(30), @categoryid NVARCHAR(MAX) OUTPUT', @PartNo, @PPFno,@Dimension, 
@categoryid OUTPUT

edited Jul 18, 2018 at 9:15

answered Jul 18, 2018 at 8:19

Diado

2,2583 gold badges22 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mr.J Over a year ago

Msg 102, Level 15, State 1, Procedure saveProdPropSampDta, Line 38 Incorrect syntax near '+'. it points out in distinct ' + @colname + ' part. Thank you for your time.

Diado Over a year ago

My mistake, you need to concatenate the query string separately to the sp_executesql call. I've updated the answer accordingly.

Collectives™ on Stack Overflow

How to include a variable data into a SQL Statement

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related