0

I have this stored procedure and I want to find a way so that I wouldn't need to provide columns names at all while executing Crates_GetForUser

ALTER procedure [dbo].[GetSomething]
(
    @UserID VARCHAR(200),
    @URLFilter varchar(256)
)
AS

--Temp table definition must match the output from the Crates_GetForUser stored procedure
CREATE TABLE #CratesTemp
(
   ID int, 
   Title nvarchar(255), 
   URL nvarchar(255), 
   CrateID int, 
   AESKey varchar(100),
   UserIsBuyer int, 
   ClientID int, 
   PromotionName nvarchar(255), 
   DisablePrinting bit, 
   DisableEmailing bit
)

INSERT #CratesTemp (ID, Title, URL, CrateID,AESKey, UserIsBuyer, ClientID, PromotionName, DisablePrinting, DisableEmailing)
    EXEC Crates_GetForUser @UserID, @URLFilter

SELECT #CratesTemp.ClientID, #CratesTemp.CrateID, #CratesTemp.ID AS AutoID, Fruits.* FROM Fruits 
JOIN #CratesTemp ON Fruits.ListUrl LIKE #CratesTemp.URL + '%' OR Fruits.SiteUrl LIKE #CratesTemp.URL + '%'

Also is it best practice to use this when you can easily use C# code to call the existing stored procedure and then create a new stored procedure to get common stuff.

To me above looks like a SQL code smell, (not developed by me..)

Edit

Maybe I can use Select into but I don't want to provide SQL server or database name at all. THanks, can I do something like this, as I am getting syntax errors,

Mathematics
  • 7,314
  • 25
  • 77
  • 152
  • 2
    Could you show us the code of the called SP (Crates_GetForUser)? Very often it is a very bad idea to use a SP just for getting data. Most SPs doing so can easily be transfered into an UDF (best choice "single-statement") or a VIEW. If you have to stick with your SP as it is you could think about an own type (look CREATE TYPE) – Shnugo Sep 14 '15 at 09:29
  • @Shnugo Crates_GetForUser is a large SP it self, which I am actually working on, but my changes broke this SP and all i want to do is to make it work regardless of columns returned by Crates_GetForUser SP, i suggested creating own type too to the developer who developed it, but you do know, it's a never ending Arguement... anyway for now I just want to focus resolving current issue really :) – Mathematics Sep 14 '15 at 09:33
  • If that SP internally expects those fields, you're out of luck: you will need to create them. I also agree with @Shnugo, but I understand the optimal solution isn't always feasible. – Rubens Farias Sep 14 '15 at 09:40
  • Although [it is technically possible](http://stackoverflow.com/a/1228165/1048425), it is definitely more of a hack. It would be better to keep track of where the stored procedure is used (`sys.sql_expression_dependencies`) when altering the output, and configure the dependencies according to the change being made. – GarethD Sep 14 '15 at 09:42
  • @GarethD I am not expert but can I not get result of SP in a variable and then use select statement to put it in a table with required columns, this way I can ignore not needed columns in select statement, but I am not sure about syntax => moving forward – Mathematics Sep 14 '15 at 09:44
  • @PleaseTeach, the biggest misadvantege of SPs is exactly this: If you want to continue with their output you need ugly things, no inline approach possible. The second biggest is: If you have done the ugly things it will never be performant due to the fact that the optimizer cannot predict its output. Really, I have no idea why so many people use SPs for a SELECT job... – Shnugo Sep 14 '15 at 09:55
  • I find it referenced here as well in code smell https://github.com/red-gate/SQL-code-smells#85-relying-on-the-insertexec-statement – Mathematics Sep 14 '15 at 10:40

0 Answers0