dynamic sql quotations
-
declare @cmd varchar(5000)
declare @dbname sysname
declare @login sysname
set @dbname = 'test'
set @login = 'lname'
select @cmd = 'use ' + @dbname + ' declare @uid int, @cmd varchar(500), @name sysname ' +
'if exists (select * from sysusers where sid = suser_sid(''' + @login + ''') and isaliased =0) ' +
'begin select @uid = uid, @name =name from ' +
'sysusers where sid = suser_sid(''' + @login + ''') If exists (select * from sysobjects ' +
'where uid = 1 and name in (select name from sysobjects where uid = @uid)) ' +
'Begin Print '' The following objects are owned by the user in database ' + @dbname + ':'' ' +
'Select convert(varchar(50), name) ''name'', type from sysobjects where uid = @uid ' +
' End '
exec (@cmd)
I get this msg:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'End'.
how does the quotation markings done when trying to do dynamic sql - when to use single quotes and when double quotes....
Thanks.
-
Not really sure, but have you tried leaving off the the concatenation of 'END' from your dynamic sql?
-
Sorry, missed the begin further inside the code. I am reformatting your code to see what else may be going on.
-
Try this:
select @cmd = 'use ' + @dbname + '; declare @uid int, @cmd varchar(500), @name sysname ' +
'if exists (select * from dbo.sysusers where sid = suser_sid(''' + @login + ''') and isaliased = 0) ' +
'begin select @uid = uid, @name = name from dbo.sysusers where sid = suser_sid(''' + @login + ''')' +
' If exists (select * from dbo.sysobjects ' +
'where uid = 1 and name in (select name from dbo.sysobjects where uid = @uid)) ' +
'Begin Print '' The following objects are owned by the user in database ' + @dbname + ':'' ' +
'Select convert(varchar(50), name) ''name'', type from dbo.sysobjects where uid = @uid ' +
' End End'
print @cmd
exec (@cmd)
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply