Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Creating Stored Procedure with SELECT ... inside Expand / Collapse
Author
Message
Posted Wednesday, January 27, 2010 11:16 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 7, 2014 12:10 PM
Points: 134, Visits: 346
Here is my stored procedure so far:

IF OBJECT_ID('dbo.usp_shipability') IS NOT NULL
DROP PROCEDURE dbo.usp_shipability
GO

CREATE PROCEDURE dbo.usp_shipability (@product_id AS int=0, @qty AS dbo.pqty=0, @nReturn dbo.percentage=0 OUT)
AS
DECLARE @onhand dbo.pqty,
@onorder dbo.pqty,
@onpicklist dbo.pqty,
@zsvDFQ dbo.pqty,
@zsvShp_in_DFQ bit

IF @product_id = 0 OR @product_id IS NULL GOTO EarlyOut

SELECT p.p_nmbr,
ps.onhand,
ps.onorder,
ps.onpicklist,
iss.df_qty AS zsvDFQ,
c.ship_in_def_qty_fl AS zsvShip_in_DFQ
FROM product p
inner join product_Stat ps ON p.product_id = ps.product_id
INNER JOIN inventory_spec iss on p.product_id = iss.product_id
inner join company c on c.company_id = p.company_id
WHERE p.product_id = @product_id
--INTO cursor #test

SET @nReturn = query.zsvDFQ

*****
How do I get the values from the select statement into vars that can be work with so that I can return a single amount?

If I put the "into cursor #test", it tells me illegal word near into.

Any ideas would be great.

Thanks,
Mike
Post #854936
Posted Wednesday, January 27, 2010 11:31 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, September 30, 2013 2:09 AM
Points: 3,131, Visits: 1,058
Select the values into #table and work with #table




Post #854942
Posted Thursday, January 28, 2010 12:29 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 7, 2014 12:10 PM
Points: 134, Visits: 346
I can't use an INTO clause on the select statement. How to I get it into #table?
Post #854958
Posted Thursday, January 28, 2010 12:58 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, February 19, 2013 2:02 AM
Points: 846, Visits: 303
To get the syntax correct, you should remove the word cursor and move the line starting with INTO before the from statement, similar to:

SELECT columns
INTO #temptable --!
FROM mytable
WHERE 1 = 0;

But, if I understand correctly what you are trying to achieve, you don't need a cursor or a temp table. Try this:

CREATE PROCEDURE tmp
@col1 INT OUTPUT
,@col2 VARCHAR(100) OUTPUT
AS
SET NOCOUNT ON ;

SELECT @col1 = col1
,@col2 = col2
FROM mytable
WHERE ...;

To test it, run the following script:

DECLARE @var1 INT
,@var2 VARCHAR(100) ;
EXEC tmp
@var1 OUTPUT
,@var2 OUTPUT ;
SELECT @var1
,@var2 ;

An alternative to using output variables that will also work if you want to return values from more than 1 record is:

CREATE PROCEDURE tmp
AS
SET NOCOUNT ON ;

SELECT col1, col2
FROM mytable
WHERE ...;

And for the calling part:

CREATE TABLE #tmp (
col1 INT
,col2 VARCHAR(100)) ;

INSERT INTO #tmp
EXEC tmp ;

SELECT *
FROM #tmp ;


Regards,

Willem
http://wschampheleer.wordpress.com
Post #854972
Posted Thursday, January 28, 2010 1:55 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:14 AM
Points: 42,460, Visits: 35,520
mike 57299 (1/27/2010)
How do I get the values from the select statement into vars that can be work with so that I can return a single amount?


What are you trying to do here/ I'm asking about the actual problem that you're trying to solve, as there may be another way to go about it.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #854989
Posted Thursday, January 28, 2010 11:47 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 7, 2014 12:10 PM
Points: 134, Visits: 346
What I am trying to accomplish is the following:

Input: product ID
Output: calculated value

Process:
Use input to look up up product information distributed across 3 tables. Do some calculations. The calculations create one final number. Return that number.

I would love to have this as a function so that I can do something like the following:

select Product.product, ufn_ship( product.product_id) AS shipnum, ....

or @xyz = ufn_ship( 1234)

Thanks for any help.

Mike
Post #855534
Posted Thursday, January 28, 2010 12:43 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, November 4, 2012 12:23 PM
Points: 2,087, Visits: 3,932
Hi

Guess you are looking for a scalar function, not a procedure. This enables you to directly return a value into a scalar variable.

-- scalar function
DROP FUNCTION GetAmount;
GO
CREATE FUNCTION GetAmount(@productId INT)
RETURNS DECIMAL(15,5)
AS
BEGIN
RETURN 1.234;
END
GO

DECLARE @amount DECIMAL(15,5);
SELECT @amount = dbo.GetAmount(123);
PRINT @amount;
GO

ATTENTION
Do NOT use those kind of scalar functions within a SELECT statement that returns much rows. The function will be called for each row.

To get data from a SELECT statement into a variable try this.
-- get SELECT into a variable
DECLARE @table_name NVARCHAR(128);

SELECT TOP(1)
@table_name = TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES;

PRINT @table_name;

Greets
Flo



The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
Post #855576
Posted Sunday, January 31, 2010 4:40 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:55 AM
Points: 11,192, Visits: 11,095
If you are looking to simulate a computed column across three tables (and it sounds as if you are), consider a view:

USE tempdb;
GO
-- Three related tables
CREATE TABLE dbo.T1 (PK INT IDENTITY PRIMARY KEY, A INT NULL);
CREATE TABLE dbo.T2 (PK INT IDENTITY PRIMARY KEY, B INT NULL);
CREATE TABLE dbo.T3 (PK INT IDENTITY PRIMARY KEY, C INT NULL);
GO
-- Sample data
INSERT dbo.T1 (A) VALUES (1), (2), (3);
INSERT dbo.T2 (B) VALUES (-4), (-5), (-6);
INSERT dbo.T3 (C) VALUES (14), (28), (36);
GO
-- View to create a sort of 'cross-table' computed column
-- Index the view if appropriate, to materialize the 'computed column'
CREATE VIEW dbo.V
WITH SCHEMABINDING
AS
SELECT T1.PK,
Value = T1.A * PI() + T2.B + LOG10(T3.C)
FROM dbo.T1
JOIN dbo.T2 ON T2.PK = T1.PK
JOIN dbo.T3 ON T3.PK = T2.PK;
GO
-- Show the contents of the view
SELECT PK, Value
FROM dbo.V;
GO
-- Tidy up
DROP VIEW dbo.V;
DROP TABLE dbo.T1, dbo.T2, dbo.T3;

Do not be tempted to use a T-SQL UDF to access data. Bad things will happen to you.




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #856790
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse