sql as a string - 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

General SQL Server Forums

New to SQL Server Programming

sql as a string

Author

Topic

BendJoe
Posting Yak Master

128 Posts

Posted - 2008-10-10 : 11:02:44

Declare @SQL varchar(200)
Set @SQL='Usp_List''SELECT ALL'','0','0','0';
Exec @SQL

I am trying to run a stored proc with USP_List as the name and it take 4 parameter
one string SELECT ALL
then three integers 0,0,0.
I am having trouble with the proper quotes and commas could some one help me
Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-10 : 11:16:46

Declare @SQL varchar(200)
Set @SQL='Usp_List''SELECT ALL'',0,0,0';
Exec @SQL

Madhivanan

Failing to plan is Planning to fail

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-10-10 : 11:32:46

I think you need


Declare @SQL varchar(200)
Set @SQL='EXEC Usp_List ''SELECT ALL'',0,0,0';
Exec (@SQL)

-- EDIT -- I tested Madh's suggestion and it does work. I thought you might need a space between the end of the procedure name and the first paramater but apparently you don't.

I'm not having a good day today.
-------------
Charlie

BendJoe
Posting Yak Master

128 Posts

Posted - 2008-10-10 : 11:41:07

Declare @SQL varchar(200)
--SEt @SQL='Usp_SearchByAll_Additional_Management''ArticleComments'',''EXP''';
Set @SQL='Usp_Activities_All_Listing''SELECT ALL ACTIVITIES'',0,0,0';
--Set @SQL='Usp_List''SELECT ALL'',0,0,0';
Exec @SQL
Msg 2812, Level 16, State 62, Line 5
Could not find stored procedure 'Usp_Activities_All_Listing'SELECT ALL ACTIVITIES',0,0,0'.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-10-10 : 11:47:48

Try changing EXEC @sql to EXEC (@sql)

-------------
Charlie

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-10 : 11:50:37

whats the purpose of using dynamic sql here?

BendJoe
Posting Yak Master

128 Posts

Posted - 2008-10-10 : 11:51:37

yes that worked
Thanks

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-10-10 : 11:52:25

Tut, Tut, Tut visakh16.

stop asking sensible questions. Just pony up and produce SQL on demand like the rest of us.

-------------
Charlie

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-10 : 11:54:19

quote:
Originally posted by Transact Charlie

Tut, Tut, Tut visakh16.

stop asking sensible questions. Just pony up and produce SQL on demand like the rest of us.

-------------
Charlie

i couldnt resist myself

i cant find a reason why OP should be doing it this way

BendJoe
Posting Yak Master

128 Posts

Posted - 2008-10-10 : 15:43:52

quote:
Originally posted by visakh16

quote:
Originally posted by Transact Charlie

Tut, Tut, Tut visakh16.

stop asking sensible questions. Just pony up and produce SQL on demand like the rest of us.

-------------
Charlie

i couldnt resist myself
i cant find a reason why OP should be doing it this way

It was part of a larger stored proc.
I was debugging it and found something wrong with the dynamic sql.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-14 : 02:41:39

quote:
Originally posted by Transact Charlie

I think you need

Declare @SQL varchar(200)
Set @SQL='EXEC Usp_List ''SELECT ALL'',0,0,0';
Exec (@SQL)

-- EDIT -- I tested Madh's suggestion and it does work. I thought you might need a space between the end of the procedure name and the first paramater but apparently you don't.

I'm not having a good day today.
-------------
Charlie

No Problem. Space is not a matter

select*from(select 1 as data)as t

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources