Getting Return Code from a stored procedure

How do I get the return code of a stored procedure,
Declare @.rtn as integer
set @.rtn = Execute myProcedure
does not work.
Is there an @.@...... value that I can check for the return code
Which is set when I execute a Return 1 in my code
I would like to use it like a function as I am having problems using
getdate() in a function.Declare @.rtn as integer
EXEC @.rtn = myProcedure
SELECT @.rtn
if @.rtn is 0, result is success.
Good luck.
"dpc"?? ??? ??:

> How do I get the return code of a stored procedure,
> Declare @.rtn as integer
> set @.rtn = Execute myProcedure
> does not work.
> Is there an @.@...... value that I can check for the return code
> Which is set when I execute a Return 1 in my code
> I would like to use it like a function as I am having problems using
> getdate() in a function.
>|||
> How do I get the return code of a stored procedure,
> Declare @.rtn as integer
> set @.rtn = Execute myProcedure
Try:
DECLARE @.rtn as integer
EXECUTE @.rtn = myProcedure

> I would like to use it like a function as I am having problems using
> getdate() in a function.
I don't understand this part of your question. Can you elaborate?
Hope this helps.
Dan Guzman
SQL Server MVP
"dpc" <dpc@.discussions.microsoft.com> wrote in message
news:490C4821-598B-4FB6-9C95-62EED799D8D3@.microsoft.com...
> How do I get the return code of a stored procedure,
> Declare @.rtn as integer
> set @.rtn = Execute myProcedure
> does not work.
> Is there an @.@...... value that I can check for the return code
> Which is set when I execute a Return 1 in my code
> I would like to use it like a function as I am having problems using
> getdate() in a function.
>|||DECLARE @.rtn as interger
EXECUTE @.rtn = myProcedure
Martin C K Poon
Senior Analyst Programmer
====================================
"dpc" <dpc@.discussions.microsoft.com> bl
news:490C4821-598B-4FB6-9C95-62EED799D8D3@.microsoft.com g...
> How do I get the return code of a stored procedure,
> Declare @.rtn as integer
> set @.rtn = Execute myProcedure
> does not work.
> Is there an @.@...... value that I can check for the return code
> Which is set when I execute a Return 1 in my code
> I would like to use it like a function as I am having problems using
> getdate() in a function.
>|||Why would you want to use getdate in your function?
UDFs don't allow non-deterministic function in their definition. and
getdate() is one.
If you want to use getdate inside your function, pass it as an input
parameter to your function (and send getdate() when you are calling the
function)
But if really want to use the current date within the function and you don't
want to accept it as parameter, then try this.. (a work around, thats all :)
create view curdate
as
select getdate() as now
and use it in the function..
--example
create function fngetdate()
returns datetime
as
begin
declare @.a datetime
select @.a = now from curdate
return (@.a)
end
--call to function
select dbo.fngetdate()
Hope this helps.

getting last 2 months date..but dont have datefield in table

Hi groups,
I forget to declare datefield in my employee table...but now in implementati
on stage...my customer is asking me to list the last two months inserted dat
e from employee table. How i can get the last 2 months inserted rows from a
table which is not having a datefield/time..
Is there any hidden data say inserted datetime etc for each rows ...?
please suggest
--
Mhsh Kumr. R> Is there any hidden data say inserted datetime etc for each rows ...?
No, unless you have a backup from two months ago and can compare it to
today's data...|||Hi
You will not have this information anywhere.
You can tell ur customer that it needs a fix and introduce a new column with
timestamp field atleast now.
u can update the timestamp with previous date or current date.
Or u might be having employee join date, u can fetch data based on that
please let me know if you would like to know anything else
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Maheshkumar.R" wrote:

> Hi groups,
> I forget to declare datefield in my employee table...but now in implementation s
tage...my customer is asking me to list the last two months inserted date from emplo
yee table. How i can get the last 2 months inserted rows from a table which is not h
avi
ng a datefield/time..
> Is there any hidden data say inserted datetime etc for each rows ...?
'
> please suggest
> --
> M?hésh Kum?r|||> introduce a new column with
> timestamp field atleast now.
Just to clarify, DATETIME or SMALLDATETIME (not the poorly-named TIMESTAMP
data type).|||No, no, no. "Time stamp field" is actually a less known painting by Dali.
ML

Getting Error while using exec statement

Hi

I am trying to use exec statement like this

Code Snippet

declare @.a varchar(10)

declare @.b varchar(10)

declare @.c varchar(20)

set @.a = 'SomeData'

select @.c = '@.b=' +'@.a'

exec ('select ' + @.c)

but i am getting this error

Must declare the variable '@.a'.

well we can achieve above problem using

Code Snippet

set @.a = 'SomeData'

select @.b=@.a

select @.b

but i am expecting like this (because i am building a formula)

exec ('select ' + @.c)

please advice

Thanks

You have a scope problem; the '@.a' variable is not known in the domain of your exec string. The scope of the code that is run as part of an "exec string" is completely separate from the scope of the code that compiled the exec string. Therefore, none of your variables -- @.a, @.b, or @.c -- have any meaning from within the execution scope of what gets done while your "exec string" is being performed. You might want to use the "sp_executesql" stored procedure in this case so that you might be able to use these variables as parameters.

Here are some previous posts on the subject:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1087502&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=660466&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=578918&SiteID=1

You also need to understand that there are some inherent problems with dynamic SQL. This article might provide some insights:

Dynamic SQL - The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html

|||

I've used like this

Code Snippet

set @.a = 'SomeData'

select @.c = '@.b=' +'@.a'

EXEC sp_executesql N'select @.c'

but this time i got this Error

Must declare the variable '@.c'.

please advice

Thanks

|||

You must also pass the parameter to sp_executesql (any the parameters included in the string.

Try something like this:

Code Snippet

set @.a = 'SomeData'

select @.c = '@.b=' +'@.a'

EXECUTE sp_executesql N'SELECT @.c', @.c

Of course, SELECT @.B='SomeData' isn't going to make a lot of sense and will cause an error.

But hey, you just asked for help with passing the parameter -not writing valide SQL statements...

|||

The following sample might help you to understand..

Code Snippet

Declare @.a as int

Declare @.b as int

Declare @.result as varchar(100)

Declare @.FormulaQuery as nvarchar(100)

Set @.a = 10

Set @.b = 20

select @.FormulaQuery = 'Set @.result = (@.b + @.a)'

EXECUTE sp_executesql

@.FormulaQuery --Your Computable Expression/Formula

, N'@.a int, @.b int, @.result int output' -- All the Param definitions

, @.a -- First Param value

, @.b -- Second Param value

, @.result output -- Third Param value which return the value from the expression

Select @.result

Getting Error 208 on Table variable in select clause

I have a stored procedure that contains a table variable.
Declare @.xxxx table
(columname columtype)
In the stored procedure there is a select against the table variable after
it has rows inserted into it. When the stored procedure is called via .Net
code, we are seeing in Profiler 208 errors. When I run the stored procedure
in Query Analyzer no errors show up. But when I do a show execution plan in
Query Analyzer, I see the plan for the select against the table variable has
the table scan in RED. When I place the cursor over the table scan it says
that the "Warning: Statistics missing for this table. Choose 'Create Missin
g
Statistics' from the contents (Right click) menu". If I try to do that I ge
t
an error saying missing object to create statistics on, which of course make
s
perfect sense.
If I change the table variable to a temporary table, the warning goes away,
but we are not sure about the 208 error just yet as I still have to have the
developers call a different stored proc to test it.
I would prefer to not change the table variables to temporary tables since
in these cases we have chosen table variables because no index was needed fo
r
speed.
NOTE: Not all of the table variables in the same stored procedure is having
the missing statistics showing up in the query execution plan.
I would appreciate any help from anyone else who has seen this before.
Thanks
LeAnneTable variables do not amntain statistics like temporary tables can.
INF: Frequently Asked Questions - SQL Server 2000 - Table Variables
http://support.microsoft.com/defaul...7&Product=sql2k
AMB
"LeAnne Jergensen" wrote:

> I have a stored procedure that contains a table variable.
> Declare @.xxxx table
> (columname columtype)
> In the stored procedure there is a select against the table variable after
> it has rows inserted into it. When the stored procedure is called via .Ne
t
> code, we are seeing in Profiler 208 errors. When I run the stored procedu
re
> in Query Analyzer no errors show up. But when I do a show execution plan
in
> Query Analyzer, I see the plan for the select against the table variable h
as
> the table scan in RED. When I place the cursor over the table scan it say
s
> that the "Warning: Statistics missing for this table. Choose 'Create Miss
ing
> Statistics' from the contents (Right click) menu". If I try to do that I
get
> an error saying missing object to create statistics on, which of course ma
kes
> perfect sense.
> If I change the table variable to a temporary table, the warning goes away
,
> but we are not sure about the 208 error just yet as I still have to have t
he
> developers call a different stored proc to test it.
> I would prefer to not change the table variables to temporary tables since
> in these cases we have chosen table variables because no index was needed
for
> speed.
> NOTE: Not all of the table variables in the same stored procedure is havin
g
> the missing statistics showing up in the query execution plan.
>
> I would appreciate any help from anyone else who has seen this before.
> Thanks
> LeAnne|||I know that table variables do not have stats. That is why I am so
:
1. Why is the explain plan showing that the stats are missing on something
they can not even exist on. Seems like a bug to me.
2. Why the Error 208 is generated on a table variable having missing stats
when they can not have stats to begin with. Seems like another bug to me.
Is anyone else seeing this or know if there is a bug that is going to be
fixed?
"Alejandro Mesa" wrote:
> Table variables do not amntain statistics like temporary tables can.
> INF: Frequently Asked Questions - SQL Server 2000 - Table Variables
> [url]http://support.microsoft.com/default.aspx?scid=kb;en-us;305977&Product=sql2k[/ur
l]
>
> AMB
> "LeAnne Jergensen" wrote:
>