How, When, Why: N'@Somevar', or syntax hell...

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

Transact-SQL (2000)

How, When, Why: N'@Somevar', or syntax hell...

Author

Topic

bbondi
Starting Member

20 Posts

Posted - 2005-04-29 : 16:18:04

I've been searching but there are som many N' (don't, can't...) the search is useless.

I am trying to see if using the
EXECUTE @RetCode = sp_executesql @SQLString, @ParamDefinition
approach to test an in-house stored procedure.

I know the sp I want to call has 3 params:
sp_Add_Commision_Stats(
@StepId int, -- Step that is being started
@CommDate DateTime, -- End date of period being processed.
@Country Char(2)) -- Country being processed.

I declare @StepId int, @End datetime, @CountryCode char(2)

I then prepare the params:
SET @SQLString = N'AddCommStat @StepId @End @CountryCode'
SET @ParAmDefinition = N'@StepId @End @CountryCode'

I execute the whole thing:
EXECUTE @RC = sp_executesql @SQLString, @ParamDefinition

The results displayed:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '@End'
@RC is: 170

Geez, this is occult-like stuff

Please, point me into the light...

Thanks,
Bob

Do the Social Engineering teams need QA? Naw, let 'em have fun!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-04-29 : 16:24:24

some commas between parameters would be nice...

Go with the flow & have fun! Else fight the flow

bbondi
Starting Member

20 Posts

Posted - 2005-04-29 : 16:34:54

Hi spirit1,
results from your suggestion:
If I add commas for the sqlsrting: N'AddCommStat @StepId, @End, @CountryCode'
Line 1: Incorrect syntax near '@End'.

If I do commas on ParamDefinition: N'@StepId, @End, @CountryCode'
Line 1: Incorrect syntax near ','.

This is the syntax hell I'm spinning thru. I've had the type in the param list, and several N' pairs between commas, etc... ACK

Do the Social Engineering teams need QA? Naw, let 'em have fun!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-04-29 : 17:05:49

try this:
SET @SQLString = N'AddCommStat @StepId, @End, @CountryCode'
SET @ParAmDefinition = N'@StepId int, @End datetime, @CountryCode char(2)'

EXECUTE @RC = sp_executesql @SQLString, @ParamDefinition, @StepId = 2, @End = getdate(), @CountryCode = 'uk'

where 2, getdate() and 'uk' are your input values

Go with the flow & have fun! Else fight the flow

bbondi
Starting Member

20 Posts

Posted - 2005-04-29 : 17:55:22

OK, I pasted the guts, so you can see what I had been doing with this effort. Where you had, @StepId = '2' I was just setting @StepId like this: select @StepId = 1 then just using @StepId - cuz I thought it contained a value, or is it in another scope? Which would mean using the @StepId = 2 method. I tried the use of a different var, say @SI and then did: @StepId = @SI. Which is the way I have it in this post; which results in:
Line 1: Incorrect syntax near '@End'.
=================================================================================
ALTER Proc ut_cg_testAddCommStat
AS
DECLARE @SQLString NVARCHAR(500)
DECLARE @ParamDefinition NVARCHAR(500)
-- SET-UP SECTION
Declare @CountryCode char(2)
Declare @End datetime
Declare @StepId int
Declare @RC int
select @CountryCode = 'US' --sorry nobody in the UK yet :)
select @StepId = 1
select @RC = 0
select @End=comm_end from CommPeriod where period_id =
(select min(period_id) from CommPeriod
where period_closed = 0 and CountryCode=@CountryCode)
and CountryCode = @CountryCode
Print 'DEBUG--> @End is: ' + cast(@End as char)
DECLARE @SI int
DECLARE @E datetime
DECLARE @CC char(2)
set @SI = @StepId --kludging
set @E = @End --kludging
set @CC = @CountryCode --kludging
SET @SQLString = N'EXEC AddCommStat @StepId @End @CountryCode'
SET @ParamDefinition = N'@StepId int, @End datetime, @CountryCode char(2)'
EXECUTE @RC = sp_executesql @SQLString, @ParamDefinition, @StepId=@SI, @End=@E, @CountryCode=@CC
IF @RC = 0
BEGIN
print 'PASS'
print '@RC is: ' + cast(@RC as char)
END
ELSE
BEGIN
print 'FAIL'
print '@RC is: ' + cast(@RC as char)
--EXEC tsu_failure 'Gotta decide what the ut_cg_testAddCommStat msg is.'
END

Do the Social Engineering teams need QA? Naw, let 'em have fun!

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-05-02 : 00:52:09

quote:

SET @SQLString = N'EXEC AddCommStat @StepId @End @CountryCode'

SET @SQLString = 'EXEC AddCommStat ' + @StepId + ',' + @End + ',' + @CountryCode

--edit
addComStat being a procedure, i assume

--------------------
keeping it simple...

bbondi
Starting Member

20 Posts

Posted - 2005-05-02 : 12:14:18

Yes, it is a proc. Here is it signature:
ALTER Proc AddCommStat (
@StepId int, -- Step that is being started
@CommDate DateTime, -- End date of period being processed.
@Country Char(2) -- Country being processed.
) as

After editing to your comment I get:
Syntax error converting the varchar value 'EXEC AddCommStat ' to a column of data type int.
I am brain-dead now :( this seems so straight forward! Ack!!!

Do the Social Engineering teams need QA? Naw, let 'em have fun!

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-05-02 : 22:05:36

what's this for?

quote:

EXECUTE @RC = sp_executesql @SQLString, @ParamDefinition, @StepId=@SI, @End=@E, @CountryCode=@CC

can you just call it as:

exec @rc=sp_executesql @sqlstring ?
where @SQLString = 'exec AddCommStat ' + @StepId + ',' + @End + ',' + @CountryCode

--------------------
keeping it simple...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources