SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Procedure with parameter
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Meshter
Starting Member

2 Posts

Posted - 11/28/2012 :  09:07:50  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Germany
8765 Posts

Posted - 11/28/2012 :  10:18:13  Show Profile  Visit webfred's Homepage  Reply with Quote
See here:
http://www.sommarskog.se/dynamic_sql.html


Too old to Rock'n'Roll too young to die.
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 11/28/2012 :  11:31:38  Show Profile  Reply with Quote
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
Go to Top of Page

Meshter
Starting Member

2 Posts

Posted - 11/28/2012 :  14:04:03  Show Profile  Reply with Quote
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'  

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000