help with this syntax - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

help with this syntax

Author

Topic

avipenina
Starting Member

44 Posts

Posted - 2010-05-28 : 04:56:19

Hi,

how i can write this syntax right? i need to exclude some databases that i'm going to insert in a table on specific column.so this is the example

THX

Declare @param1 nvarchar(30),
@SQL nvarchar(max)
set @param1 = 'master,model'
set @SQL = 'select name from sys.databases where name not in ('+@param1+')'
exec sp_executesql @SQL

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-05-28 : 05:09:23

Try this


Declare @param1 nvarchar(30),
@SQL nvarchar(max)
set @param1 = 'master,model'
set @SQL = 'select name from sys.databases where name not in ('''+REPLACE(@param1,',',''',''') +''')'
exec sp_executesql @SQL

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-05-28 : 10:47:00

Also refer this to know, how single quotes work in Dynamic SQL
http://beyondrelational.com/blogs/madhivanan/archive/2008/02/19/understanding-single-quotes.aspx

Madhivanan

Failing to plan is Planning to fail

avipenina
Starting Member

44 Posts

Posted - 2010-05-28 : 11:36:57

quote:
Originally posted by vaibhavktiwari83

Try this

Declare @param1 nvarchar(30),
@SQL nvarchar(max)
set @param1 = 'master,model'
set @SQL = 'select name from sys.databases where name not in ('''+REPLACE(@param1,',',''',''') +''')'
exec sp_executesql @SQL

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

can i do this select statement without the dynamic sql?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-28 : 11:46:08

You can insert your values in the parm into a temp table and join with sys.databases to SELECT the ones missing.

avipenina
Starting Member

44 Posts

Posted - 2010-05-28 : 12:18:26

ok,how i can insert the @param1 into table when they are like that 'master,model' ?

and can i use a simple select statement without the dynamic sql?
something like that

Declare @param1 nvarchar(30),
@SQL nvarchar(max)
set @param1 = 'master,model'
select name from sys.databases where name not in ('''+REPLACE(@param1,',',''',''') +''')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources