December 9, 2009 at 11:45 am
Hi,
I consider myself fairly good at T-SQL. However, i've been trying to get a ridiculously simply stored procedure to send back a value for a few hours. Can someone help?
* * * *
alter proc [dbo].[sp_test_Called]
@vEffectiveBegin datetime output,
@vEffectiveEnd datetime output,
@vDate1 datetime,
@vDate2 datetime
as
set nocount on
print '@vDate1: ' + convert(varchar, @vDate1)
print '@vDate2: ' + convert(varchar, @vDate2)
set @vEffectiveBegin = @vDate1
set @vEffectiveEnd = @vDate2
print 'inside sp_test_Called'
print '@vEffectiveBegin: ' + convert(varchar, @vEffectiveBegin)
print '@vEffectiveEnd: ' + convert(varchar, @vEffectiveEnd)
print ' '
return
alter proc [dbo].[sp_Test_Callee]
as
declare @vEffectiveBegin datetime
declare @vEffectiveEnd datetime
declare @vEffectiveBegin_1 datetime
declare @vEffectiveEnd_1 datetime
declare @vOur_Date_1 datetime
declare @vOur_Date_2 datetime
set @vEffectiveBegin_1 = convert(datetime, '1/1/1900')
set @vEffectiveEnd_1 = convert(datetime, '1/1/1900')
set @vOur_Date_1 = convert(datetime, '11/07/2008')
set @vOur_Date_2 = convert(datetime, '11/15/2008')
exec sp_test_Called
@vEffectiveBegin=@vEffectiveBegin_1,
@vEffectiveEnd=@vEffectiveEnd_1,
@vDate1=@vOur_Date_1,
@vDate2=@vOur_Date_2
print GetDate()
print 'back inside sp_Test_Callee'
print '@vEffectiveBegin: ' + convert(varchar, @vEffectiveBegin)
print '@vEffectiveEnd: ' + convert(varchar, @vEffectiveEnd)
select effectiveBegin = @vEffectiveBegin
return
* * * *
I've set permissions on sp_test_Called and sp_Test_Callee for the public role to be able to execute them. When I login as 'sa' on our SQL 2005 server, I open up a query window and enter this command:
exec sp_Test_Callee
My output:
@vDate1: Nov 7 2008 12:00AM
@vDate2: Nov 15 2008 12:00AM
inside sp_test_Called
@vEffectiveBegin: Nov 7 2008 12:00AM
@vEffectiveEnd: Nov 15 2008 12:00AM
Dec 9 2009 1:15PM
back inside sp_Test_Callee
effectiveBegin
-----------------------
NULL
(1 row(s) affected)
...
Now, how come'effectiveBegin doesn't come back (from 'sp_Test_Callee') with the value that was given to it in 'sp_Test_called'? I have made it an output parameter.
thanks,
December 9, 2009 at 11:52 am
Check BOL for the syntax of the EXECUTE statement. I think you'll find that you need the OUTPUT statement on the parameters that are output parameters when calling the stored proc, sp_test_Called.
December 9, 2009 at 12:01 pm
To be specific, you'd need to change this:
exec sp_test_Called
@vEffectiveBegin=@vEffectiveBegin_1,
@vEffectiveEnd=@vEffectiveEnd_1,
@vDate1=@vOur_Date_1,
@vDate2=@vOur_Date_2
to this:
exec sp_test_Called
@vEffectiveBegin=@vEffectiveBegin_1 output,
@vEffectiveEnd=@vEffectiveEnd_1 output,
@vDate1=@vOur_Date_1,
@vDate2=@vOur_Date_2
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 9, 2009 at 12:04 pm
Hi Lynn,
I changed the call to be:
exec sp_test_Called
@vEffectiveBegin=@vEffectiveBegin_1 output,
@vEffectiveEnd=@vEffectiveEnd_1 output,
@vDate1=@vOur_Date_1,
@vDate2=@vOur_Date_2
this makes my calling stored proc look like this:
alter proc [dbo].[sp_Test_Callee]
as
declare @vEffectiveBegin datetime
declare @vEffectiveEnd datetime
declare @vEffectiveBegin_1 datetime
declare @vEffectiveEnd_1 datetime
declare @vOur_Date_1 datetime
declare @vOur_Date_2 datetime
set @vEffectiveBegin_1 = convert(datetime, '1/1/1900')
set @vEffectiveEnd_1 = convert(datetime, '1/1/1900')
set @vOur_Date_1 = convert(datetime, '11/07/2008')
set @vOur_Date_2 = convert(datetime, '11/15/2008')
exec sp_test_Called
@vEffectiveBegin=@vEffectiveBegin_1 output,
@vEffectiveEnd=@vEffectiveEnd_1 output,
@vDate1=@vOur_Date_1,
@vDate2=@vOur_Date_2
print GetDate()
print 'back inside sp_Test_Callee'
print '@vEffectiveBegin: ' + convert(varchar, @vEffectiveBegin)
print '@vEffectiveEnd: ' + convert(varchar, @vEffectiveEnd)
select effectiveBegin = @vEffectiveBegin
return
* * *
unfortunately, i'm still getting the same result. 🙁
December 9, 2009 at 12:06 pm
Thank you, Gus. There are times I can't post certain code snippets from work, and this was one of them. Can't include d r o p t a b l e statements either. Don't understand it, only happens here at work.
(I actually had to put spaces in there to get this to post!!)
December 9, 2009 at 12:07 pm
hi GSquared,
I tried your suggestion. unfortunately, i'm still not getting the parameter values back as intended.
thanks,
December 9, 2009 at 2:24 pm
Lynn Pettis (12/9/2009)
Thank you, Gus. There are times I can't post certain code snippets from work, and this was one of them. Can't include d r o p t a b l e statements either. Don't understand it, only happens here at work.(I actually had to put spaces in there to get this to post!!)
Probably some weird SQL injection security measure on your internet connection.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 9, 2009 at 2:27 pm
cafescott (12/9/2009)
hi GSquared,I tried your suggestion. unfortunately, i'm still not getting the parameter values back as intended.
thanks,
You're selecting @vEffectiveBegin, but you're not assigning a value to that. It's just a parameter. The return value gets assigned to the variable after the equal sign in the parameters statement.
Final select should be changed to:
select effectiveBegin = @vEffectiveBegin_1
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 10, 2009 at 6:31 am
thanks, GSquared, it now works perfectly.
I appreciate the tip.
🙂
December 10, 2009 at 7:29 am
You're welcome.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply