Help Needed in dynamic sql

Question

Help Needed in dynamic sql

KGJ-Dev

SSCertifiable

Points: 5780
More actions
November 29, 2015 at 11:27 am

#305699

Hi,
below is my sample query
declare @Query nvarchar(max)
DECLARE @Names VARCHAR(8000)
Declare @tablename nvarchar(50) = 'sample',@columnname nvarchar(50) = 'userid',@Condition nvarchar = 'name = test'
SET @Query = ' SELECT ' + @Names + '= COALESCE( @Names + '', '', '''' ) + CAST( ' + @columnname + 'AS VARCHAR ) as value ' +
' FROM ' + @tablename +
' WHERE ' + @Condition
Exec (@Query)
I am not getting any result. can anyone please help me on verifying this dynamic sql conversion.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

KGJ-Dev (11/29/2015)
Hi,
below is my sample query
declare @Query nvarchar(max)
DECLARE @Names VARCHAR(8000)
Declare @tablename nvarchar(50) = 'sample',@columnname nvarchar(50) = 'userid',@Condition nvarchar = 'name = test'
SET @Query = ' SELECT ' + @Names + '= COALESCE( @Names + '', '', '''' ) + CAST( ' + @columnname + 'AS VARCHAR ) as value ' +
' FROM ' + @tablename +
' WHERE ' + @Condition
Exec (@Query)
I am not getting any result. can anyone please help me on verifying this dynamic sql conversion.

What is it that you're looking for as a result? Are you simply looking for a concatenated CSV list of things using a variable table name and variable column name?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ed Wagner SSC Guru Points: 287026 More actions · Answer 2

The reason you aren't getting any results is that you aren't executing a SQL statement.

When corrected, you may get some results, but you'll also be open to SQL injection.

Jeff's question is perfect. Focus on what you want to get before focusing on how to get it.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

KGJ-Dev SSCertifiable Points: 5780 More actions · Answer 3

Hi Jeff,

Thanks for your reply and basically i am planning to create generic function which will take "table name","column name to makecomma separated" and "where condition" and send the result as comma separated values.

sample data to play with:

DEclare @sample Table(UserId int,name varchar(50))

insert into @sample

select 1, 'Test1' union all

select 2, 'Test1' union all

select 3, 'Test1' union all

select 4, 'Test1' union all

select 5, 'Test2' union all

select 6, 'Test3'

declare @Query nvarchar(max)

DECLARE @Names VARCHAR(8000)

Declare @tablename nvarchar(50) = '@sample',@columnname nvarchar(50) = 'userid',@Condition nvarchar = 'name = Test1'

SET @Query = ' SELECT ' + @Names + '= COALESCE( @Names + '', '', '''' ) + CAST( ' + @columnname + 'AS VARCHAR ) as value ' +

' FROM ' + @tablename +

' WHERE ' + @Condition

exec sp_executeSql @Query

select @Names

Expected output: 1,2,3,4,5,6

any suggestion please how to achieve this logic.

matak SSCrazy Points: 2674 More actions · Answer 4

Your @Names variable is null which means joining this into your dynamic query will make the whole statement NULL.

You also do not have a space in this part @columnname + 'AS VARCHAR which is causing a syntax error.

Also @Condition nvarchar = 'name = Test1' will only set @Condition to 'n' - you need to specify a size and you need to add single quotes to Test1

Something like this may help you out a bit

DEclare @sample Table(UserId int,name varchar(50))

insert into @sample

select 1, 'Test1' union all

select 2, 'Test1' union all

select 3, 'Test1' union all

select 4, 'Test1' union all

select 5, 'Test2' union all

select 6, 'Test3'

declare @Query nvarchar(max)

DECLARE @Names VARCHAR(8000)

Declare @tablename nvarchar(50) = '@sample',@columnname nvarchar(50) = 'userid',@Condition nvarchar(50) = 'name = ''Test1'''

SET @Query = ' SELECT COALESCE( @Names + '', '', '''' ) + CAST( ' + @columnname + ' AS VARCHAR ) as value ' +

' FROM ' + @tablename +

' WHERE ' + @Condition

select @Query

matak SSCrazy Points: 2674 More actions · Answer 5

matak

SSCrazy

Points: 2674

November 29, 2015 at 7:04 pm

#1842775

Double post sorry.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 6

KGJ-Dev (11/29/2015)
Hi Jeff,
Thanks for your reply and basically i am planning to create generic function which will take "table name","column name to makecomma separated" and "where condition" and send the result as comma separated values.

I can show you the dynamic SQL to pull this off but 1) it will always have to be a real table or a temp table because dynamic SQL doesn't like table variables (different scope) and 2) you won't be able to build a generic FUNCTION out of it because it's not possible (I hate saying that because someone will find a work around) to execute dynamic SQL in any kind of user defined function.

As already pointed out, it will also need to be protected from SQL Injection.

With all that in mind, I'd like to recommend that you just build a template around the code from the following article and write hard-code when you need it.

http://www.sqlservercentral.com/articles/comma+separated+list/71700/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

KGJ-Dev SSCertifiable Points: 5780 More actions · Answer 7

Hi Jeff,

I would be using this against real table and not temp table. just to play with test data i gave temp variable and data on my sample. Still wondering why this couldn't be achieved using udf? Any sample for my requirement please.

thanks

Luis Cazares SSC Guru Points: 183706 More actions · Answer 8

KGJ-Dev (11/30/2015)
Hi Jeff,
I would be using this against real table and not temp table. just to play with test data i gave temp variable and data on my sample. Still wondering why this couldn't be achieved using udf? Any sample for my requirement please.
thanks

Because UDFs don't allow the EXECUTE command, which is needed for dynamic sql.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

KGJ-Dev SSCertifiable Points: 5780 More actions · Answer 9

Oops. I totally forgot that. Thanks for reminding me Luis...

Sorry Jeff for the confusion.

ScottPletcher SSC Guru Points: 101099 More actions · Answer 10

I'd create such a proc in the master db and mark it as a system object so that it could be used in all dbs.

I'd also at least:

1) optionally allow multiple table names;

2) optionally allow a schema name with the table name: you can use PARSENAME() to split them;

3) optionally allow a row count to be specified: this would be particularly useful for initial testing against a new table.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".