June 19, 2009 at 1:13 am
I am not sure how to explain this,, but i'll give it a go.
I have a stored procedure ee_updatestatus as below:
create PROCEDURE [dbo].[ee_updateStatus]
(
@v_assetId varchar(25),
@v_status varchar(1),
@v_timestamp datetime,
@v_debugLevelint = 0,
@v_dummytag float
)
It updates the status of the assets.
i want to pass these parameters to the stored procedure:
@v_assetid=select assetid from asset where assettype like '%unit%'
@v_status='1'
@v_timestamp=getdate()
@v_debuglevel=5
@v_dummytag=0
I am not sure how to include this select statement in the stored procedure.. I have got the resoltution but that is through cursor.. i was wondering if it can be done without the cursors.
June 19, 2009 at 1:32 am
when you create a SP and want to put values at run time then you declare the SP as the way you have shown :
create PROCEDURE [dbo].[ee_updateStatus]
(
@v_assetId varchar(25),
@v_status varchar(1),
@v_timestamp datetime,
@v_debugLevel int = 0,
@v_dummytag float
)
as
if you want to pass the values to these variables then do this
create proc [dbo].[ee_updateStatus]
as
declare
@v_assetid varchar (max),
@v_status varchar (1),
@v_timestamp datetime,
@v_debuglevel int,
@v_dummytag int
set @v_status='1'
set @v_timestamp=getdate()
set @v_debuglevel=5
set @v_dummytag=0
set @v_assetid ='select assetid from asset where assettype like "%unit%"'
Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)
June 19, 2009 at 1:41 am
Thank you abhay for your response.
i don't really want to amend my existing stored procedure or create a new stored procedure.
i tried the following:
select 'exec ee_updatestatus',assetid,',', '1',',', getdate(),',', '5',',' ,'0' from asset where assettype like '%unit%'
and then copied the result sets and ran the query, but that is giving me an error message as date needs to be in ' '.
June 19, 2009 at 3:52 am
If you're only going to pass getdate() each time then just declare it as a variable inside the sp and don't pass it so you don't get the error.
June 19, 2009 at 4:08 am
Sorry guys,, i can't really amend the stored procedure as it gets fired every minute when certain events happen.
Hence i am taking this approach.
The result i get is:
exec ee_updatestatus12,1,2009-06-19 11:02:59.923,5,0
exec ee_updatestatus14,1,2009-06-19 11:02:59.923,5,0
Running this is giving me errors as well.. The issues are:
1. getdate() needs to be in '2009-06-19 11:02:59.923 '
2. Need to get rid of the spaces.
Thanks.
June 19, 2009 at 4:22 am
I don't know if I've got this right but you could either try
select 'exec ee_updatestatus',',', '1',', ''', getdate(),''' ,', '5',',' ,'0'
if you just want the text or just create a seperate stored procedure where you can pass the parameters you want and put it all together using some dynamic SQL and execute it from within a second sp.
something like:
SET @cmd = 'select ''exec ee_updatestatus'','','', ''1'','', '''','+ getdate()+','''' ,'', ''5'','','' ,''0'
EXEC (@cmd)
June 19, 2009 at 4:36 am
I think u've understood it right..
Your select statement gives me following error messages:
Msg 8114, Level 16, State 1, Procedure ee_updateStatus, Line 0
Error converting data type varchar to datetime.
Msg 8114, Level 16, State 1, Procedure ee_updateStatus, Line 0
Error converting data type varchar to datetime.
the result set is as below.
I take the space off from before and after the date, it works fine.
exec ee_updatestatusFiller_1,1, '2009-06-19 11:34:26.173' ,5,0
exec ee_updatestatusPacker_1,1, '2009-06-19 11:34:26.173' ,5,0
If i take the spaces before and after date out, it works fine.
June 19, 2009 at 8:40 am
Sorry, using a different account to answer your question from home but it's still BU69 here!!!!:-D
I did wonder about the spaces but I couldn't think of any way round it so that's why I suggested a seperate stored procedure to run your sp from so it does a lot of the work for you in which case you could use different parameters to pass to the sp and for the date you could either pass a date or if no date is passed then the default for the query is getdate().
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy