How to set value of variable in sql query

Question

How can I use custom function in sql query to set value of some variable.

My function is:

function myFunc(par){return par;}

My SQL query is:

 const string query =
            @"
            DECLARE @result varchar(100)
            SET @result= myFunc(N'test')

            INSERT[dbo].[TempTable] ([Name]) 
            VALUES (@result)
            ";

2014 and btw this code is written in Migration, not in Configuration.cs class — ruud
– ruud, Commented Apr 17, 2016 at 9:18
In SQL Server, you need to specify the schema name when calling the function . . . dbo.myfunc(N'test'). — Gordon Linoff
– Gordon Linoff, Commented Apr 17, 2016 at 11:11

Stuart J Cuthbertson · Accepted Answer · 2016-04-17 17:39:10Z

1

You just need to specify the schema that this function belongs to. Assuming it is in schema dbo, just change this line:

SET @result= myFunc(N'test')

to

SET @result= dbo.myFunc(N'test')

If you like, you can half the size of the overall query by inserting directly from the function output - no @variable required:

INSERT INTO dbo.TempTable (Name) 
SELECT dbo.myFunc(N'test')

edited Apr 17, 2016 at 17:39

answered Apr 17, 2016 at 9:57

Stuart J Cuthbertson

4386 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ruud Over a year ago

I do that and get the following error: Cannot find either column "myFunc" or the user-defined function or aggregate "myFunc", or the name is ambiguous.

Stuart J Cuthbertson Over a year ago

I have updated my answer; I'd answered on-the-fly and misunderstood what the problem was. @GordonLinoff was correct in his comment.

Collectives™ on Stack Overflow

How to set value of variable in sql query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related