Executing dynamic query inside a scalar function sql server

Question

I am trying to make a scalar function in SQL Server. I have coined up the select statement as follows:

SET @Statement1 = 'SELECT 1 FROM ' + @p_TableName + 
                  ' WHERE RegionTypeID = 1 AND RegionID = ' + 
                  CONVERT (nvarchar (50), @p_RegionID)

Here @p_TableName is the name of the table (nvarchar (500)) and @p_RegionID is a uniqueidentifier.

I am now executing the above statement as follows:

EXEC @ReturnValue1 = @Statement1

WHERE @ReturnValue1 is int.

But when I call this function from elsewhere I get this error:

The name 'SELECT 1 FROM [PS].[Availability] WHERE RegionTypeID = 1 AND RegionID = AF4C182C-F751-41AD-AA6A-20A50A7A38C8' is not a valid identifier.

I need to know how I can call a dynamic sql select statement from within a scalar function.

Dynamic sql is not permitted in UDFs. Check here for a good reference on the topic. — Giorgos Betsos
– Giorgos Betsos, Commented May 23, 2016 at 7:13
Thanks...Can you please suggest a workaround for my problem? Is there any other way with which I can write the select statement given above? All I need is to know if the RegionTypeID and RegionID is present? — Sarin Gopalan
– Sarin Gopalan, Commented May 23, 2016 at 7:16
@SarinGopalan you can use stored procedure (SP) instead of function. The results of SP can be written in table then, if you need that. — gofr1
– gofr1, Commented May 23, 2016 at 7:34
I need to use this function along a select statement...so I have to stick to functions only! — Sarin Gopalan
– Sarin Gopalan, Commented May 23, 2016 at 7:38
@SarinGopalan check my answer, after executing SP you wil get single value that you can use next in some other query. If this is not what you need, please, specify what you need to do with the output you get. — gofr1
– gofr1, Commented May 23, 2016 at 9:50

gofr1 · Accepted Answer · 2016-05-23 09:47:38Z

You can create SP like below with output parameter:

CREATE PROCEDURE ProcNameGoesHere 
    @p_TableName nvarchar(500),
    @p_RegionID uniqueidentifier,
    @output int OUTPUT
AS
BEGIN
    DECLARE @Statement1 nvarchar(max), 
            @ParmDefinition nvarchar(500)

    SET @Statement1 = N'SELECT @someValue = 1 FROM ' + @p_TableName + 
                      ' WHERE RegionTypeID = 1 AND RegionID = ' + 
                      CONVERT (nvarchar (50), @p_RegionID)  
    SET @ParmDefinition = N'@someValue int OUTPUT'

    EXEC sp_executesql @Statement1, @ParmDefinition, @output=@someValue OUTPUT 

    RETURN;

END

Then you can call it like this:

DECLARE @output int

EXEC ProcNameGoesHere '[PS].[Availability]','AF4C182C-F751-41AD-AA6A-20A50A7A38C8', @output OUTPUT

SELECT @output

Or:

IF ISNULL(@output,0) = 1
BEGIN
    --Do something here
END

Collectives™ on Stack Overflow

Executing dynamic query inside a scalar function sql server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related