stored procedure with output - SQL Server Forums

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

Import/Export (DTS) and Replication (2000)

stored procedure with output

Author

Topic

betski
Starting Member

3 Posts

Posted - 2005-11-16 : 09:18:13

Hi all,

am quite new at this DTS stuff...hope you can help me out as i am completely stuck!

What i'm trying to do is creating a dts package (obviously!) with the following tasks in it:

1. A stored procedure / sql query that returns 0 or 1 depending on a value in the database (a column in the first row of the table). This is an easy task to create on its own, and i guess i could easily add an sql task in my package as well.

2. A dts-task (already created) which is run only if the stored procedure / query returns 1. If the query returns 0, i want to end the execution of the package without running the dts-task.

I need help with the workflow here...how do i use the 1 or 0 returned from my query? Hope i have explained it well enough for you to understand. Maybe i'm way off...i don't know since i haven't done this before!

Thankful for any help!

/Lisa

Vivaldi
Constraint Violating Yak Guru

298 Posts

Posted - 2005-11-16 : 09:43:02

Create a global variables to hold the number...

in the SQL Task, choose Parameters, and specify the output parameter for the query (Select columnthathas1init from table)

then have a workflow to your next sql task with the proc. Right click on the workflow and you can change the active x script for the work flow to only run it if the global variable is set appropriatly.

________________________________________________

SQL = Serious Quaffing of Liquor

betski
Starting Member

3 Posts

Posted - 2005-11-16 : 10:47:34

Sounds great...but i still need some help. I can't quite figure out how to create the parameter. Problem is, i'm not just picking up a value from the table. I need to find out whether two values are null or not. If col1 is null and col2 is not null for a certain row, then i return 1, if not, return 0. This is the value that i want as my output parameter. I have created a stored procedure:

CREATE PROC getValueFromTable @value int OUTPUT
AS
IF (SELECT col1 FROM myTable WHERE colID = (SELECT MAX(colID) FROM myTable)) IS NOT NULL
AND (SELECT col2 FROM myTable WHERE colID= (SELECT MAX(colID) FROM myTable)) IS NULL
BEGIN
SET @value = 1
RETURN
END
ELSE
BEGIN
SET @value = 0
RETURN
END
GO

@value is the value i want to check. Is this possible?

thanks!
/L.

Vivaldi
Constraint Violating Yak Guru

298 Posts

Posted - 2005-11-16 : 11:04:21

quote:
Originally posted by betski

Sounds great...but i still need some help. I can't quite figure out how to create the parameter. Problem is, i'm not just picking up a value from the table. I need to find out whether two values are null or not. If col1 is null and col2 is not null for a certain row, then i return 1, if not, return 0. This is the value that i want as my output parameter. I have created a stored procedure:

thanks!
/L.

sqldts.com has some good starters on how to setup the parameter returning, this will help you get moving along.
http://www.sqldts.com/default.aspx?234

________________________________________________

SQL = Serious Quaffing of Liquor

betski
Starting Member

3 Posts

Posted - 2005-11-17 : 07:31:26

I have now managed to create a task that sets a global variable to 1 or 0 depending on the returned result of the sql task. I can't figure out how to get the workflow to run only if the variable is 1, though. When i right click on the workflow and choose Properties, all i see is a tab called precedence, and i can't see how i'd be able to use this. Please help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources