August 9, 2004 at 5:11 am
I want to achieve something like :
IF
BEGIN
CREATE PROCEDURE sp1
....
END
ELSE
BEGIN
CREATE PROCEDURE sp2
....
END
Can anyone give an example, please?
August 9, 2004 at 5:38 am
declare @strsql nvarchar(4000)
if (select system_user)='mike'
begin
print 'True'
set @strsql='create procedure usp_test as select NULL'
exec sp_executesql @strsql
end
else
begin
print 'False'
set @strsql='create procedure usp_test as select NULL'
exec sp_executesql @strsql
end
MVDBA
August 9, 2004 at 6:05 am
Thanks. Unfortunately, I forgot to mention that the SPs are big. I think executesql is limited to 8K which will not be enough.
Thanks, anyway.
August 9, 2004 at 6:09 am
you could write out the stored procedures to text files and then use ISQLW.exe to run them.
that way you can use xp_cmdshell to call ISQLW
MVDBA
August 9, 2004 at 7:04 am
That looks interesting - I'll give it a try. Thanks for the help.
August 10, 2004 at 12:13 pm
Might be possible to create the large procs independently and then conditionally execute them with dynamic sql
August 11, 2004 at 1:35 am
Not appropriate in this sceanrio, but thanks for the suggestion - it certainly could be an option in other cases.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply