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