| Author |
Topic |
|
KilpAr
Yak Posting Veteran
80 Posts |
 Posted - 2012-04-30 : 08:09:41
|
I have finally completed the query I need to make. I used almost 8000 characters in that, including 29 SELECT statements. I have the very same CASE...WHEN...THEN...END statement there eight times, each taking 443 characters. Can I name that part somehow, like where I have thatSELECT something * CASE(and here's the huge statement) as myresult FROM blahblah as something likeSELECT something * SQUEEZED as myresult FROM blahblah DEFINE FUNCTION SQUEEZED = CASE(and here's the huge statement) ? (those *'s are multiply operators in case it wasn't clear)Even better, can I pass arguments for that or for a named query somehow? If you have a link to some kind of tutorial, I'd be ok with that too. |
|
|
vinu.vijayan
Posting Yak Master
227 Posts |
Posted - 2012-04-30 : 08:31:45
|
| Yes, this can be done using Table Valued Functions like this:[url]http://msdn.microsoft.com/en-us/library/ms191165.aspx[/url]Or by using Stored Procedure having input and output parameters like this:[url]http://sqlserverpedia.com/wiki/Stored_Procedures_-_Output_Parameters_%26_Return_Values[/url]Using the Stored Procedure would be a little different when compared to what you want to do in terms of the approach, but anyday it is a better bet than using a Table Valued Function.N 28° 33' 11.93148"E 77° 14' 33.66384" |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-04-30 : 09:56:33
|
| I think you're compelled to use CASE(and here's the huge statement) because of lack of mapping table. On most cases such big case statements can be replaced by creating a mapping table which can be joined to current query and performance would also be much better than the huge CASE...WHENAnyways posting some sample data to illustrate exact scenario will help us to give more clearer solution------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-04-30 : 09:58:54
|
quote:
Originally posted by vinu.vijayan
Yes, this can be done using Table Valued Functions like this:[url]http://msdn.microsoft.com/en-us/library/ms191165.aspx[/url]Or by using Stored Procedure having input and output parameters like this:[url]http://sqlserverpedia.com/wiki/Stored_Procedures_-_Output_Parameters_%26_Return_Values[/url]Using the Stored Procedure would be a little different when compared to what you want to do in terms of the approach, but anyday it is a better bet than using a Table Valued Function.N 28° 33' 11.93148"E 77° 14' 33.66384"
Using stored procedure with OUTPUT parameters is not analogous to Table Valued Function. OUTPUT parameters can return only single value at a time so you wont be able to return table resultset as in former case. For that you need to just retrieve it as SELECT statement within the proc and use INSERT...EXEC outside to get it onto a table having exact structure as your resultset------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
vinu.vijayan
Posting Yak Master
227 Posts |
Posted - 2012-05-01 : 00:55:53
|
Yes you are right Visakh. Was concentrating too much on typing the post that I completely missed the Mismatch.But, you missed this part in my reply:quote:
Using the Stored Procedure would be a little different when compared to what you want to do in terms of the approach
N 28° 33' 11.93148"E 77° 14' 33.66384" |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-05-01 : 10:08:19
|
quote:
Originally posted by vinu.vijayan
Yes you are right Visakh. Was concentrating too much on typing the post that I completely missed the Mismatch.But, you missed this part in my reply:quote:
Using the Stored Procedure would be a little different when compared to what you want to do in terms of the approach
N 28° 33' 11.93148"E 77° 14' 33.66384"
That still doesnt imply my point------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2012-05-01 : 18:21:27
|
| If you had one SELECT that used your scriptlet 29 times, then a CTE might be the way to go. Since it is the other way around, have you considered dumping the results of your scriplet into a temp table and then referencing the temp table in your 29 SELECTS? You could even apply some proper indexing sfter the temp table was populated.=================================================There is a foolish corner in the brain of the wisest man. -Aristotle, philosopher (384-322 BCE) |
|
|
|
vinu.vijayan
Posting Yak Master
227 Posts |
Posted - 2012-05-02 : 00:19:01
|
quote:
Originally posted by visakh16
quote:
Originally posted by vinu.vijayan
Yes you are right Visakh. Was concentrating too much on typing the post that I completely missed the Mismatch.But, you missed this part in my reply:quote:
Using the Stored Procedure would be a little different when compared to what you want to do in terms of the approach
N 28° 33' 11.93148"E 77° 14' 33.66384"
That still doesnt imply my point------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
What I meant by Difference in approach between using Stored Procedure with output parameters and Table valued Functions is exactly what you wrote in your last post:quote:
Using stored procedure with OUTPUT parameters is not analogous to Table Valued Function. OUTPUT parameters can return only single value at a time so you wont be able to return table resultset as in former case. For that you need to just retrieve it as SELECT statement within the proc and use INSERT...EXEC outside to get it onto a table having exact structure as your resultset
N 28° 33' 11.93148"E 77° 14' 33.66384" |
|
|
|
|
Named functions in T-SQL?