Pass Parameter inside Stored Procedure

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 2008 Forums

Transact-SQL (2008)

Pass Parameter inside Stored Procedure

Author

Topic

Vack
Aged Yak Warrior

530 Posts

Posted - 2012-06-12 : 14:04:26

When I execute the Stored procedure below I am getting blank rows. It executes with no errors and I get prompted for a year, just not returning data.

I'm pretty sure its because my parameter in the stored procedure is not getting passed down to:
where year(inv_dt)=@YEAR

If I replace @YEAR with 2002 I get data.


ALTER PROCEDURE [dbo].[MonthlySales] 
-- Add the parameters for the stored procedure here
	@YEAR int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
DECLARE @listCol VARCHAR(2000)
DECLARE @query VARCHAR(4000)
SELECT  @listCol = STUFF(( SELECT DISTINCT

                                '],[' + ltrim(str(Month(inv_dt)))

                        FROM    oehdrhst_sql

                        ORDER BY '],[' + ltrim(str(Month(inv_dt)))

                        FOR XML PATH('')

                                    ), 1, 2, '') + ']'
SET @query =
'
Declare @YEAR int


SELECT * FROM

      (SELECT cus_no,slspsn_no, Month(inv_dt) OrderYear, tot_sls_amt

            FROM oehdrhst_sql
          where year(inv_dt)=@YEAR

            ) src

PIVOT (SUM(tot_sls_amt) FOR OrderYear

IN ('+@listCol+')) AS pvt'

 

EXECUTE (@query)
END

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-06-12 : 14:18:58

1. temporarily replace
--EXECUTE (@query)
with
PRINT @query
2. Run query manually in SSMS using exec MonthlySales 2012
3. Post back result of PRINT

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

Vack
Aged Yak Warrior

530 Posts

Posted - 2012-06-12 : 14:25:11

I got a few things back. Not sure which one you were looking for.
-----1st
USE [001]
GO

DECLARE @return_value int

EXEC @return_value = [dbo].[MonthlySales]
@YEAR = 2002

SELECT 'Return Value' = @return_value

GO

----------2nd
Declare @YEAR int

SELECT * FROM

(SELECT cus_no,slspsn_no, Month(inv_dt) OrderYear, tot_sls_amt

FROM oehdrhst_sql
where year(inv_dt)=@YEAR

) src

PIVOT (SUM(tot_sls_amt) FOR OrderYear

IN ([1],[2],[3],[4],[5])) AS pvt

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-06-12 : 14:28:28

there you go you see there it says @year. it should show the value of @year not the name of the parameter @year itself.
you need to wrap @year just like you are doing @listCol


SET @query =
'
Declare @YEAR int


SELECT * FROM

      (SELECT cus_no,slspsn_no, Month(inv_dt) OrderYear, tot_sls_amt

            FROM oehdrhst_sql
          where year(inv_dt)='  + CAST(@YEAR varchar(4)) +  '

            ) src

PIVOT (SUM(tot_sls_amt) FOR OrderYear

IN ('+@listCol+')) AS pvt'

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

Vack
Aged Yak Warrior

530 Posts

Posted - 2012-06-12 : 14:30:27

I'm not sure I understand.

Running your code gives me Incorrect syntax near varchar

Vack
Aged Yak Warrior

530 Posts

Posted - 2012-06-12 : 14:38:39

I was able to figure it out.

That worked. Thanks for your help

where year(inv_dt)='+CAST(@year as varchar(4))+'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources