Procedure with parameter - 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

Procedure with parameter

Author

Topic

Meshter
Starting Member

2 Posts

Posted - 2012-11-28 : 09:07:50

Hi all,
I've been trying to write a procedure that uses the parameter in the name of several variables and a table, but do not know the right syntax. It goes something like this:

CREATE PROCEDURE [dbo].P @year INT
AS
SELECT T2.f1,
Coalesce(sum(f2), 0) as ctr@year,
.....
--a few more similar lines
....
INTO dbo.T3@year

FROM T1 t1, T2 t2
Where f3 >= f4-10000*@year AND
f3 < f5-10000*@year
group by T2.f1

Obviously, the above code does not work the way I want it to (it does work in SAS, where the original code was written).

So, question 1: how can I make it work?
question 2: is there a better, more elegant/efficient way to accomplish the same task?

To give you more details, the fields f3, f4 and f5 above are dates as integers, and what I need is some totals over each of 3 prior years, later to be all added to the same table, indexed by f1.
Sorry for the long post!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-11-28 : 10:18:13

See here:
http://www.sommarskog.se/dynamic_sql.html

Too old to Rock'n'Roll too young to die.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-11-28 : 11:31:38

I must be missing someting as I don't see why this needs to be dynamic sql. Can you post DDL, DML and expected output, that will help to hel you better. If you are unfamilar with how to do that, here are some links that can help:

http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Meshter
Starting Member

2 Posts

Posted - 2012-11-28 : 14:04:03

Thanks for the link, webfred, it's good read! I believe that I could make it work, but maybe there is an easier way?

So, here is a simplified way to look at my problem. I have a table as below, where I want to summarize by id several variables (C1-C8, L1-L8). Now, I would like to do it for each of several years back (3 for now, but let's say n in general), which is where my "ldate between effdate-10000*@n and expdate-10000*@n" is needed. How can I do this with a single procedure that creates a table with unique id and C1_P1, C1_P2,..., C1_Pn, L1_P1...L1_Pn as field names?
Hope this makes sense.



create table dbo.mytable (id int, effdate int, expdate int, ldate int, C1 int, C2 int)

insert into dbo.mytable 

(id, effdate, expdate, ldate, C1, C2)

 SELECT '29','20101017','20110417','20110305','0','1' UNION ALL
SELECT '29','20101017','20110417','20110305','0','1' UNION ALL
SELECT '36','20091128','20101128','20100916','1','0' UNION ALL
SELECT '61','20090526','20100526','20090810','0','1' UNION ALL
SELECT '61','20090526','20100526','20090810','0','1' UNION ALL
SELECT '35','20070405','20071005','20070903','1','0' UNION ALL
SELECT '61','20090705','20100105','20090919','0','1' UNION ALL
SELECT '61','20090705','20100105','20090919','0','1' UNION ALL
SELECT '67','20080719','20090119','20081017','0','1' UNION ALL
SELECT '56','20090516','20100516','20090914','0','1' UNION ALL
SELECT '29','20100503','20101103','20100726','0','1'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources