2

I am using SQL Server 2012, i have a script by which i am inserting values to a table, in that script i have to convert the format of some DateTime variables on the basis of two parameters.

I can do it using CASE or if condition in sql. I am not allowed to make any Function or procedure in the database to which i can refer.

Is there any other way like creating a temporary function or temporary procedure within the script to apply condition alter the format for Datetime values?

Aman Chauhan
  • 95
  • 1
  • 3
  • 12
  • If you can do it using `case` why do you want a temporary function/procedure? – HoneyBadger Aug 04 '16 at 13:39
  • 1
    @HoneyBadger actually temporary procedure do exist, but not functions. I have never used one as they seem totally pointless but they are available. – Sean Lange Aug 04 '16 at 13:43

1 Answers1

1

Yes you can:

CREATE PROCEDURE #usp_TempOne 
@Input INT,
@Output INT OUTPUT
as 
SET @Output = @Input * 2
RETURN
GO
DECLARE @i INT = 10, @o INT;
EXEC #usp_TempOne @i, @o OUTPUT
SELECT @o
Slava Murygin
  • 1,951
  • 1
  • 10
  • 10
  • I tried to use a temporary procedure, but i was not able to set that is returned from the stored procedure to the column. actually i am passing three values in the Temp procedure, first two are the values on the basis of which the third values format will be changed. and the returned value is to be stored in a parameter. Can you provide me the syntax to execute the temp procedure and get the value it return to a Different parameter. – Aman Chauhan Aug 04 '16 at 14:38
  • OK. Unfortunately the SP solution is somwhat limited, it doesn't go everywhere a function can (e.g. in side a select field list etc. :/ (Ran into this this afternoon wanting to write a quick throw-away /function/ to do something and found to my surprise you can't. SP won't help me as I need the calculation to be inside a join condition as well as in the output list of another query etc, for some data munging I'm doing... so a function is what's needed, not an SP) – W.Prins Apr 29 '20 at 13:16
  • I'm using temp SPs 100 times a day for 10-15 years now. You can do virtually any thing inside. Using temp tables. It might be not the best from performance perspective, but you still can do it. – Slava Murygin May 01 '20 at 12:56