SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to make this block dynamic?


How to make this block dynamic?

Author
Message
sqldriver
sqldriver
SSC Eights!
SSC Eights! (972 reputation)SSC Eights! (972 reputation)SSC Eights! (972 reputation)SSC Eights! (972 reputation)SSC Eights! (972 reputation)SSC Eights! (972 reputation)SSC Eights! (972 reputation)SSC Eights! (972 reputation)

Group: General Forum Members
Points: 972 Visits: 2518
I'm just not sure how to do it.

When I run:


select @liststr = coalesce(@liststr+',' ,'') + quotename(column_name)
from [server].j3688802s.information_schema.columns where table_name = 'sample'
print (@liststr)



I get a list of column names like so:


[Id],[Queue],[PRO_PID],[PRO_FIELD1],[PRO_FIELD2],[PRO_FIELD3],[PRO_FIELD4]...


Running this returns nothing:


select @liststr = 'coalesce(' + @liststr + '+'','' ,'''') + quotename(column_name)
from ' + @server + '.' + @db + '.information_schema.columns where table_name = ''' + @table + ''''
print (@liststr)




I have all the name parts parsed correctly, I can see that in other print statements. I think I'm just processing some part of it incorrectly. Any suggestions?

Thanks
sharath.chalamgari
sharath.chalamgari
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1476 Visits: 798
are you looking for something like this


exec ('select @liststr = coalesce(@liststr+'+''''+','+''''+' ,'+''''+''''+') + quotename(column_name)
from '+ @server + '.'+ @db+ '.information_schema.columns where table_name = '+ ''''+ @table+'''')



Steven Willis
Steven Willis
SSC Eights!
SSC Eights! (825 reputation)SSC Eights! (825 reputation)SSC Eights! (825 reputation)SSC Eights! (825 reputation)SSC Eights! (825 reputation)SSC Eights! (825 reputation)SSC Eights! (825 reputation)SSC Eights! (825 reputation)

Group: General Forum Members
Points: 825 Visits: 1721
erikd (7/7/2013)
I'm just not sure how to do it.

When I run:


select @liststr = coalesce(@liststr+',' ,'') + quotename(column_name)
from [server].j3688802s.information_schema.columns where table_name = 'sample'
print (@liststr)



I get a list of column names like so:


[Id],[Queue],[PRO_PID],[PRO_FIELD1],[PRO_FIELD2],[PRO_FIELD3],[PRO_FIELD4]...


Running this returns nothing:


select @liststr = 'coalesce(' + @liststr + '+'','' ,'''') + quotename(column_name)
from ' + @server + '.' + @db + '.information_schema.columns where table_name = ''' + @table + ''''
print (@liststr)




I have all the name parts parsed correctly, I can see that in other print statements. I think I'm just processing some part of it incorrectly. Any suggestions?

Thanks


I think just setting your variable to '' will do the trick.

SET @liststr = ''
select @liststr = 'coalesce...etc

or

(' + ISNULL(@liststr,'') + '+'','' ,'''')


If @liststr is NULL the concatenation will also return NULL.

You might also consider using the FOR XML PATH method for concatentation which is often more efficient than using COALESCE.

 
jimbobmcgee
jimbobmcgee
SSC-Enthusiastic
SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)

Group: General Forum Members
Points: 130 Visits: 916
Not that I advocate this approach, at all, but the following should allow you to get a column list for any server/database/schema/table combo...


DECLARE @liststr NVARCHAR(MAX) = NULL,
@server SYSNAME = NULL,
@db SYSNAME = N'Scratch',
@schema SYSNAME = N'dbo',
@table SYSNAME = N'nodes'

DECLARE @cols TABLE (column_name SYSNAME);
DECLARE @sql NVARCHAR(MAX) = N'
SELECT QUOTENAME(column_name)
FROM ##SERVER##.##DB##.information_schema.columns
WHERE table_schema = ##SCHEMA##
AND table_name = ##TABLE##
';
SET @sql = REPLACE(REPLACE(REPLACE(REPLACE(@sql,
N'##SERVER##.', COALESCE(QUOTENAME(@server) + N'.', N'')),
N'##DB##.', COALESCE(QUOTENAME(@db) + N'.', N'')),
N'##SCHEMA##', COALESCE(QUOTENAME(@schema, N''''), N'''dbo''')),
N'##TABLE##', QUOTENAME(@table, N''''));

RAISERROR ('@sql = %s', 10, 1, @sql) WITH NOWAIT; -- debug line, can be removed

INSERT INTO @cols EXEC(@sql); -- get the list of columns

SELECT @liststr = STUFF( -- comma-separated list
(SELECT ',' + column_name FROM @cols FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),
1, 1, ''
);

PRINT(@liststr);



Note that there are any number of formats of server name, database name, schema name or table name that could lead to this not really working (especially given QUOTENAME's 50-char irritations) -- possibly even leading to a dreaded SQL injection attack.

Consider validating the values of @server, @db, @schema and @table against master.sys.servers, master.sys.databases, information_schema.schemata and information_schema.tables first.

Also note that there are nicer ways to build that SQL string, without resorting to REPLACE -- I use the REPLACE form for my clarity, but building the string in the old-fashioned way will be more robust.

(And seriously re-evaluate whether your app really needs to do this whole thing -- usually, when someone needs to break out the dynamics across servers or databases, the underlying design is fundamentally flawed)

HTH
J.
sqldriver
sqldriver
SSC Eights!
SSC Eights! (972 reputation)SSC Eights! (972 reputation)SSC Eights! (972 reputation)SSC Eights! (972 reputation)SSC Eights! (972 reputation)SSC Eights! (972 reputation)SSC Eights! (972 reputation)SSC Eights! (972 reputation)

Group: General Forum Members
Points: 972 Visits: 2518
sharath.chalamgari (7/7/2013)
are you looking for something like this


exec ('select @liststr = coalesce(@liststr+'+''''+','+''''+' ,'+''''+''''+') + quotename(column_name)
from '+ @server + '.'+ @db+ '.information_schema.columns where table_name = '+ ''''+ @table+'''')




Almost:


declare @server varchar(64)
declare @db varchar(64)
declare @table varchar(64)
declare @liststr nvarchar (max)

select @server = quotename(parsename('[server].j3688802s.dbo.sample_web', 4))
select @db = parsename('[server].j3688802s.dbo.sample_web', 3)
select @table = parsename('[server].j3688802s.dbo.sample_web', 1)

print (@server)
print (@db)
print (@table)

exec ('select @liststr = coalesce(@liststr+'+''''+','+''''+' ,'+''''+''''+') + quotename(column_name)
from '+ @server + '.'+ @db+ '.information_schema.columns where table_name = '+ ''''+ @table+'''')





[server]
j3688802s
sample_web
Msg 137, Level 15, State 1, Line 1
Must declare the scalar variable "@liststr".


I was running in to this yesterday with a different set up, and couldn't figure out why.
sqldriver
sqldriver
SSC Eights!
SSC Eights! (972 reputation)SSC Eights! (972 reputation)SSC Eights! (972 reputation)SSC Eights! (972 reputation)SSC Eights! (972 reputation)SSC Eights! (972 reputation)SSC Eights! (972 reputation)SSC Eights! (972 reputation)

Group: General Forum Members
Points: 972 Visits: 2518
Steven Willis (7/7/2013)
erikd (7/7/2013)
I'm just not sure how to do it.

When I run:


select @liststr = coalesce(@liststr+',' ,'') + quotename(column_name)
from [server].j3688802s.information_schema.columns where table_name = 'sample'
print (@liststr)



I get a list of column names like so:


[Id],[Queue],[PRO_PID],[PRO_FIELD1],[PRO_FIELD2],[PRO_FIELD3],[PRO_FIELD4]...


Running this returns nothing:


select @liststr = 'coalesce(' + @liststr + '+'','' ,'''') + quotename(column_name)
from ' + @server + '.' + @db + '.information_schema.columns where table_name = ''' + @table + ''''
print (@liststr)




I have all the name parts parsed correctly, I can see that in other print statements. I think I'm just processing some part of it incorrectly. Any suggestions?

Thanks


I think just setting your variable to '' will do the trick.

SET @liststr = ''
select @liststr = 'coalesce...etc

or

(' + ISNULL(@liststr,'') + '+'','' ,'''')


If @liststr is NULL the concatenation will also return NULL.

You might also consider using the FOR XML PATH method for concatentation which is often more efficient than using COALESCE.

 


This is close as well, but here's the result:


[server]
j3688802s
sample_web
coalesce(@liststr+',' ,'') + quotename(column_name)
from [server].j3688802s.information_schema.columns where table_name = 'sample_web'
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'coalesce'.


Also, the I've experimented with FOR XML, but it often truncates the list after so many characters. I think it's less than the 8k that normally get displayed?
sqldriver
sqldriver
SSC Eights!
SSC Eights! (972 reputation)SSC Eights! (972 reputation)SSC Eights! (972 reputation)SSC Eights! (972 reputation)SSC Eights! (972 reputation)SSC Eights! (972 reputation)SSC Eights! (972 reputation)SSC Eights! (972 reputation)

Group: General Forum Members
Points: 972 Visits: 2518
jimbobmcgee (7/8/2013)
Not that I advocate this approach, at all, but the following should allow you to get a column list for any server/database/schema/table combo...


DECLARE @liststr NVARCHAR(MAX) = NULL,
@server SYSNAME = NULL,
@db SYSNAME = N'Scratch',
@schema SYSNAME = N'dbo',
@table SYSNAME = N'nodes'

DECLARE @cols TABLE (column_name SYSNAME);
DECLARE @sql NVARCHAR(MAX) = N'
SELECT QUOTENAME(column_name)
FROM ##SERVER##.##DB##.information_schema.columns
WHERE table_schema = ##SCHEMA##
AND table_name = ##TABLE##
';
SET @sql = REPLACE(REPLACE(REPLACE(REPLACE(@sql,
N'##SERVER##.', COALESCE(QUOTENAME(@server) + N'.', N'')),
N'##DB##.', COALESCE(QUOTENAME(@db) + N'.', N'')),
N'##SCHEMA##', COALESCE(QUOTENAME(@schema, N''''), N'''dbo''')),
N'##TABLE##', QUOTENAME(@table, N''''));

RAISERROR ('@sql = %s', 10, 1, @sql) WITH NOWAIT; -- debug line, can be removed

INSERT INTO @cols EXEC(@sql); -- get the list of columns

SELECT @liststr = STUFF( -- comma-separated list
(SELECT ',' + column_name FROM @cols FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),
1, 1, ''
);

PRINT(@liststr);



Note that there are any number of formats of server name, database name, schema name or table name that could lead to this not really working (especially given QUOTENAME's 50-char irritations) -- possibly even leading to a dreaded SQL injection attack.

Consider validating the values of @server, @db, @schema and @table against master.sys.servers, master.sys.databases, information_schema.schemata and information_schema.tables first.

Also note that there are nicer ways to build that SQL string, without resorting to REPLACE -- I use the REPLACE form for my clarity, but building the string in the old-fashioned way will be more robust.

(And seriously re-evaluate whether your app really needs to do this whole thing -- usually, when someone needs to break out the dynamics across servers or databases, the underlying design is fundamentally flawed)

HTH
J.


Thanks. I'll try to work this in to what I'm doing. For background, this is the rest of the SP:

http://www.sqlservercentral.com/Forums/Topic1467819-338-1.aspx

I found that if I tried to use it FROM different server than the one I'm using, it wouldn't give me the column results because it was looking in the completely wrong spot for them. So, it might be flawed and stupid and ugly and its mother may dress it funny, but I have to do it a lot, an I'm really just trying to make the best of it.
sqldriver
sqldriver
SSC Eights!
SSC Eights! (972 reputation)SSC Eights! (972 reputation)SSC Eights! (972 reputation)SSC Eights! (972 reputation)SSC Eights! (972 reputation)SSC Eights! (972 reputation)SSC Eights! (972 reputation)SSC Eights! (972 reputation)

Group: General Forum Members
Points: 972 Visits: 2518
I got it to work by doing this:


declare @sql nvarchar (max)
declare @server varchar(64)
declare @db varchar(64)
declare @table varchar(64)

select @server = quotename(parsename('[server].j3688802s.dbo.sample_web', 4))
select @db = parsename('[server].j3688802s.dbo.sample_web', 3)
select @table = parsename('[server].j3688802s.dbo.sample_web', 1)

print (@server)
print (@db)
print (@table)


set @sql = 'Select Stuff((SELECT '', '' + quotename(column_name) AS [text()]
FROM
(SELECT column_name FROM ' + @server + '.' + @db + '.information_schema.columns
where table_name = ''' + @table + '''
) x
For XML PATH ('''')),1,1,'''')'
print (@sql)

exec sp_executesql @sql, N'@sql nvarchar(max)', @sql print (@sql)




I've never used sp_executesql before. So, I'll experiment with FOR XML, until I run into it truncating column lists.

Thanks guys!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search