January 27, 2010 at 11:16 pm
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 @onhanddbo.pqty,
@onorderdbo.pqty,
@onpicklistdbo.pqty,
@zsvDFQdbo.pqty,
@zsvShp_in_DFQbit
IF @product_id = 0 OR @product_id IS NULL GOTO EarlyOut
SELECTp.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
January 27, 2010 at 11:31 pm
Select the values into #table and work with #table
January 28, 2010 at 12:29 am
I can't use an INTO clause on the select statement. How to I get it into #table?
January 28, 2010 at 12:58 am
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 ;
January 28, 2010 at 1:55 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 28, 2010 at 11:47 am
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
January 28, 2010 at 12:43 pm
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
January 31, 2010 at 4:40 am
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
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply