EXEC fails - 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 2000 Forums

SQL Server Development (2000)

EXEC fails

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2003-01-17 : 22:22:46

I've included the whole procedure for anyone interested.

I pass an exec statement as a string in @GroupQuery. The Value of @GroupQuery is:

EXEC dbo.ad_UserSelect @CallerAdminID='18',@ClientID='1017',@CourseID='111',@Firstname='sam',@Lastname='carter',@CompletedSince='Nov 1, 2002'

I get the following error:

Server: Msg 203, Level 16, State 2, Procedure EM_GetEmailRecordset, Line 42
The name 'dbo.ad_UserSelect @CallerAdminID='18',@ClientID='1017',@CourseID='111',@Firstname='sam',@Lastname='carter',@CompletedSince='Nov 1, 2002',@Columns='Email'' is not a valid identifier.

What's up with that?

Sam
------------------------------------------------

Create Procedure dbo.EM_GetEmailRecordset
@CallerAdminID INT
, @GroupQuery VARCHAR (4000)
, @OutputMessage VARCHAR (4000) = NULL OUTPUT
AS
SET NOCOUNT ON
DECLARE @err INT ,
@RowCount INT
SET @RowCount = 123

Print @GroupQuery
set @GroupQuery = left(@GroupQuery, Len(@GroupQuery)) -- Strip any extra blanks / spaces
Print @GroupQuery
SET @GroupQuery = SUBSTRING(@GroupQuery, 6, LEN(@GroupQuery) -5) +
',@Columns=''Email''' -- JUST GIVE US THE Email column

Print @GroupQuery
select 1 where 1 = 0 -- set eof - in case we comment out the next line for testing..

Print @GroupQuery

EXEC @GroupQuery

Print 'Rowcount'
Print @RowCount
SET @OutputMessage = IsNull(@GroupQuery, 'Null Output Message')

RETURN(@Rowcount)

SET NOCOUNT OFF
GO

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-01-17 : 22:30:05

Did you use double quotes (") in the sproc? If you put a string in double quotes, it's interpreting it as a column/identifier name instead of a string literal.

SamC
White Water Yakist

3467 Posts

Posted - 2003-01-17 : 23:28:26

No double quotes. I thought of that.

The varchar command string uses double ' to delimit values.

I may have to rewrite this not to pass strings.

Sam

SamC
White Water Yakist

3467 Posts

Posted - 2003-01-19 : 10:42:40

Is the following statement legal or not?

DECLARE @Group varchar(4000)

set @Group = 'dbo.myquery @myparm=123'

exec @Group

I get the following error:

Could not find the stored procedure dbo.myquery @myparm=123

If I remove the paramter, it's found and executes.

Sam

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-01-19 : 10:52:00

Have you tried:

exec(@Group)

If the statement has any dynamic SQL in it, the parentheses are required to make it interpret the code correctly.

SamC
White Water Yakist

3467 Posts

Posted - 2003-01-19 : 13:35:21

Rob,

Thank you very much for pointing out this nuance. I'd read BOL twice and missed the significance of parenthesis for character strings on exec.

I'll eventually rewrite this proc not to use strings, but for the moment, it gets me running.

Sam

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-01-19 : 16:22:55

You may also want to consider this

From BOL:

quote:

Using sp_executesql in this procedure is more efficient than using EXECUTE to execute a string. When sp_executesql is used, there are only 12 versions of the INSERT string generated, 1 for each monthly table. With EXECUTE, each INSERT string is unique because the parameter values are different. Although both methods generate the same number of batches, the similarity of the INSERT strings generated by sp_executesql makes it more likely that the query optimizer will reuse execution plans.

SamC
White Water Yakist

3467 Posts

Posted - 2003-01-19 : 22:41:38

Thanks Valter,

Sam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources