Creating stored procedure - 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

General SQL Server Forums

New to SQL Server Programming

Creating stored procedure

Author

Topic

Vack
Aged Yak Warrior

530 Posts

Posted - 2012-06-12 : 12:32:33

How do I make the following code a stored procedure?

I want to connect a crystal report or Excel to the stored procedure and have the user prompted for the year.

the code below returns a list of customers with Sales by each month having each column a different month.


DECLARE @listCol VARCHAR(2000)
DECLARE @query VARCHAR(4000)
--DECLARE @YEAR numeric
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 =

'SELECT * FROM

      (SELECT cus_no, Month(inv_dt) OrderYear, tot_sls_amt

            FROM oehdrhst_sql
            where year(inv_dt)=2002

            ) src

PIVOT (SUM(tot_sls_amt) FOR OrderYear

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

 

EXECUTE (@query)

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-12 : 12:37:11

put
create proc mysp
as
set nocount on

before the code (you can put a go at the end if yoou like) then execute it.

It will create an SP which yoou can then excute or call from the report.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Vack
Aged Yak Warrior

530 Posts

Posted - 2012-06-12 : 12:39:25

My problem is now it only looks at the year 2002. I want a prompt for the year. I can't seem to get that to work.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-12 : 12:43:04

Sorry - missed that bit
You prompt for the year from the client (report or excel) and pass it to the SP as a parameter

create proc mysp
@year numeric
as
set nocount on

....

go

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Vack
Aged Yak Warrior

530 Posts

Posted - 2012-06-12 : 12:50:54

When I add that to my sp and execute it, I get

Must declare the scalar variable "@YEAR".

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-12 : 13:06:35

You're probably case sensitive

create proc mysp
@YEAR numeric
as
set nocount on

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Vack
Aged Yak Warrior

530 Posts

Posted - 2012-06-12 : 13:13:10

How can I pass my parameter to my Set @query = section of my code?

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)
--DECLARE @YEAR numeric
--set @YEAR = @YYEAR
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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-06-12 : 14:42:47

SET @query =

'SELECT * FROM
(SELECT cus_no,slspsn_no, Month(inv_dt) OrderYear, tot_sls_amt
FROM oehdrhst_sql
where year(inv_dt)=' + convert(char(4), @YEAR) + '
) src
PIVOT (SUM(tot_sls_amt) FOR OrderYear
IN ('+@listCol+')) AS pvt'

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources