Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Select @ReturnValue = exec ('select...

Author  Topic 

infodemers
Posting Yak Master

183 Posts

Posted - 2012-10-03 : 15:49:31
Creating the following function:

CREATE FUNCTION [dbo].[getTresholdColor](@Complex NvarChar(12),@ComponentName NvarChar(12),@Date varchar(7)) RETURNS varchar(50)

AS
BEGIN
-- Declare the variables here
DECLARE @Threshold1 NvarChar(12);
DECLARE @Threshold2 NvarChar(12);
DECLARE @ok as CHAR(3);
DECLARE @ReturnValue varchar(50);

SET @Threshold1 = 'Yellow';
SET @Threshold2 = 'Red';
SET @ok = '';

SELECT @ReturnValue = exec('SELECT tbl_Complex.ComplexName, tbl_Component.ComponentName, tbl_Management.Treshold1,
tbl_Management.Treshold2,
Case
When ' + @ComponentName + 'Limit - ' + @ComponentName + 'ProvisionedInUse <= tbl_Management.Treshold1 and ' + @ComponentName + 'Limit - ' + @ComponentName + 'ProvisionedInUse > tbl_Management.Treshold2
Then ''' + @Threshold1 + '''
When ' + @ComponentName + 'Limit - ' + @ComponentName + 'ProvisionedInUse <= tbl_Management.Treshold2
THEN ''' + @Threshold2 + '''
else ''' + @ok + '''
End as Available,
tbl_Data1.[Date]
FROM tbl_Complex INNER JOIN
tbl_Data1 ON tbl_Complex.ComplexID = tbl_Data1.ComplexID INNER JOIN
tbl_Management ON tbl_Data1.ComplexID = tbl_Management.ComplexID INNER JOIN
tbl_Component ON tbl_Management.ComponentID = tbl_Component.ComponentID
where tbl_Complex.ComplexName = ''' + @Complex + ''' and tbl_Component.ComponentName = ''' + @ComponentName +''' and Left(tbl_Data1.[Date],7) = ''' + @Date + '''
order by tbl_Complex.ComplexName, tbl_Component.ComponentName, tbl_Data1.[Date]')
RETURN @ReturnValue
END

I receive the error:
Incorrect syntax near the keyword 'exec'.

But again I receive an error :
Incorrect syntax near the keyword 'exec'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-03 : 17:25:21
you cant use dynamic sql from within a UDF. you should be making it as a procedure if you want to use it like below

Also if you want to return values through variable in dynamic sql, you need to use sp_executesql rather than EXEC

see

http://oakdome.com/programming/SQL_DynamicSQL_sp_executesql.php

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

infodemers
Posting Yak Master

183 Posts

Posted - 2012-10-04 : 08:39:27
Hi again visakh16, :-)

The reason why I wanted to use a function is because I wish to run another query and have the result from that function within that query . Quick sample would be:
Select ComplexName, Component, dbo.fn_GetTresholdColor(ComplexName, Componentname, [Date]) as Available,date from tbl_Data1

Then how could I use that Sp from within another query?

Within a stored procedure, I got it running as follow:
How could I use
ALTER PROCEDURE[dbo].[sp_Treshold]

@Complex NvarChar(12),
@ComponentName NvarChar(12),
@Date varchar(7)

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here

DECLARE @Threshold1 NvarChar(12);
DECLARE @Threshold2 NvarChar(12);
DECLARE @ok as CHAR(3);
DECLARE @SQLString NVARCHAR(1000);

SET @Threshold1 = 'Yellow';
SET @Threshold2 = 'Red';
SET @ok = '';

SET @SQLString = 'SELECT tbl_Complex.ComplexName, tbl_Component.ComponentName, tbl_Management.Treshold1,
tbl_Management.Treshold2,
Case
When ' + @ComponentName + 'Limit - ' + @ComponentName + 'ProvisionedInUse <= tbl_Management.Treshold1 and ' + @ComponentName + 'Limit - ' + @ComponentName + 'ProvisionedInUse > tbl_Management.Treshold2
Then ''' + @Threshold1 + '''
When ' + @ComponentName + 'Limit - ' + @ComponentName + 'ProvisionedInUse <= tbl_Management.Treshold2
THEN ''' + @Threshold2 + '''
else ''' + @ok + '''
End as Available,
tbl_Data1.[Date]
FROM tbl_Complex INNER JOIN
tbl_Data1 ON tbl_Complex.ComplexID = tbl_Data1.ComplexID INNER JOIN
tbl_Management ON tbl_Data1.ComplexID = tbl_Management.ComplexID INNER JOIN
tbl_Component ON tbl_Management.ComponentID = tbl_Component.ComponentID
where tbl_Complex.ComplexName = ''' + @Complex + ''' and tbl_Component.ComponentName = ''' + @ComponentName +''' and Left(tbl_Data1.[Date],7) = ''' + @Date + '''
order by tbl_Complex.ComplexName, tbl_Component.ComponentName, tbl_Data1.[Date]'
EXECUTE sp_executesql @SQLString

END
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-04 : 10:32:03
quote:
Originally posted by infodemers

Hi again visakh16, :-)

The reason why I wanted to use a function is because I wish to run another query and have the result from that function within that query . Quick sample would be:
Select ComplexName, Component, dbo.fn_GetTresholdColor(ComplexName, Componentname, [Date]) as Available,date from tbl_Data1

Then how could I use that Sp from within another query?

Within a stored procedure, I got it running as follow:
How could I use
ALTER PROCEDURE[dbo].[sp_Treshold]

@Complex NvarChar(12),
@ComponentName NvarChar(12),
@Date varchar(7)

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here

DECLARE @Threshold1 NvarChar(12);
DECLARE @Threshold2 NvarChar(12);
DECLARE @ok as CHAR(3);
DECLARE @SQLString NVARCHAR(1000);

SET @Threshold1 = 'Yellow';
SET @Threshold2 = 'Red';
SET @ok = '';

SET @SQLString = 'SELECT tbl_Complex.ComplexName, tbl_Component.ComponentName, tbl_Management.Treshold1,
tbl_Management.Treshold2,
Case
When ' + @ComponentName + 'Limit - ' + @ComponentName + 'ProvisionedInUse <= tbl_Management.Treshold1 and ' + @ComponentName + 'Limit - ' + @ComponentName + 'ProvisionedInUse > tbl_Management.Treshold2
Then ''' + @Threshold1 + '''
When ' + @ComponentName + 'Limit - ' + @ComponentName + 'ProvisionedInUse <= tbl_Management.Treshold2
THEN ''' + @Threshold2 + '''
else ''' + @ok + '''
End as Available,
tbl_Data1.[Date]
FROM tbl_Complex INNER JOIN
tbl_Data1 ON tbl_Complex.ComplexID = tbl_Data1.ComplexID INNER JOIN
tbl_Management ON tbl_Data1.ComplexID = tbl_Management.ComplexID INNER JOIN
tbl_Component ON tbl_Management.ComponentID = tbl_Component.ComponentID
where tbl_Complex.ComplexName = ''' + @Complex + ''' and tbl_Component.ComponentName = ''' + @ComponentName +''' and Left(tbl_Data1.[Date],7) = ''' + @Date + '''
order by tbl_Complex.ComplexName, tbl_Component.ComponentName, tbl_Data1.[Date]'
EXECUTE sp_executesql @SQLString

END



whats the purpose of dynamic code inside? why are your column names not static?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

infodemers
Posting Yak Master

183 Posts

Posted - 2012-10-04 : 11:14:06
I run another query to which I pass values ComplexName, ComponentName and Date. The ComponentName can be VM, VVM, ICMBType1, ICMBType2 and ICMBType3
Then based on that, I need to run that stored procedure to find out the treshold color. The column names in the table are VMLimit, VVMLimit, ICMBType1Limit, etc...
I could get rid of those variables and make them static in the code, you are correct.
DECLARE @Threshold1 NvarChar(12);
DECLARE @Threshold2 NvarChar(12);
DECLARE @ok as CHAR(3);
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-04 : 12:01:40
quote:
Originally posted by infodemers

I run another query to which I pass values ComplexName, ComponentName and Date. The ComponentName can be VM, VVM, ICMBType1, ICMBType2 and ICMBType3
Then based on that, I need to run that stored procedure to find out the treshold color. The column names in the table are VMLimit, VVMLimit, ICMBType1Limit, etc...
I could get rid of those variables and make them static in the code, you are correct.
DECLARE @Threshold1 NvarChar(12);
DECLARE @Threshold2 NvarChar(12);
DECLARE @ok as CHAR(3);



if you're making them static you could use udfs otherwise you've to change it to procedure

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

infodemers
Posting Yak Master

183 Posts

Posted - 2012-10-04 : 13:31:23

I finally cam to an ugly solution that works.
Select * , dbo.fn_getTresholdColor (ComplexName,ComponentName, Date) as available from...etc


Alter FUNCTION [dbo].[fn_getTresholdColor](@Complex NvarChar(12),@ComponentName NvarChar(12), @Date varchar(7)) RETURNS varchar(6)

AS
BEGIN

Declare @sReturnValue varchar(6);

If @ComponentName = 'vm'
Begin
SELECT @sReturnValue= (SELECT Case
When vmLimit - vmProvisionedInUse <= tbl_Management.Treshold1 and vmLimit - vmProvisionedInUse > tbl_Management.Treshold2
Then 'Yellow'
When vmLimit - vmProvisionedInUse <= tbl_Management.Treshold2
THEN 'Red'
else ''
End as Available
FROM tbl_Complex INNER JOIN
tbl_Data1 ON tbl_Complex.ComplexID = tbl_Data1.ComplexID INNER JOIN
tbl_Management ON tbl_Data1.ComplexID = tbl_Management.ComplexID INNER JOIN
tbl_Component ON tbl_Management.ComponentID = tbl_Component.ComponentID
where tbl_Complex.ComplexName = @Complex and tbl_Component.ComponentName = @ComponentName and Left(tbl_Data1.[Date],7) = @Date
)
End

ELSE IF @ComponentName = 'vvm'
Begin
SELECT @sReturnValue= (SELECT Case
When vvmLimit - vvmProvisionedInUse <= tbl_Management.Treshold1 and vvmLimit - vvmProvisionedInUse > tbl_Management.Treshold2
Then 'Yellow'
When vvmLimit - vvmProvisionedInUse <= tbl_Management.Treshold2
THEN 'Red'
else ''
End as Available
FROM tbl_Complex INNER JOIN
tbl_Data1 ON tbl_Complex.ComplexID = tbl_Data1.ComplexID INNER JOIN
tbl_Management ON tbl_Data1.ComplexID = tbl_Management.ComplexID INNER JOIN
tbl_Component ON tbl_Management.ComponentID = tbl_Component.ComponentID
where tbl_Complex.ComplexName = @Complex and tbl_Component.ComponentName = @ComponentName and Left(tbl_Data1.[Date],7) = @Date
)
End

ELSE IF @ComponentName = 'icmbType1'
Begin
SELECT @sReturnValue= (SELECT Case
When icmbType1Limit - icmbType1ProvisionedInUse <= tbl_Management.Treshold1 and icmbType1Limit - icmbType1ProvisionedInUse > tbl_Management.Treshold2
Then 'Yellow'
When icmbType1Limit - icmbType1ProvisionedInUse <= tbl_Management.Treshold2
THEN 'Red'
else ''
End as Available
FROM tbl_Complex INNER JOIN
tbl_Data1 ON tbl_Complex.ComplexID = tbl_Data1.ComplexID INNER JOIN
tbl_Management ON tbl_Data1.ComplexID = tbl_Management.ComplexID INNER JOIN
tbl_Component ON tbl_Management.ComponentID = tbl_Component.ComponentID
where tbl_Complex.ComplexName = @Complex and tbl_Component.ComponentName = @ComponentName and Left(tbl_Data1.[Date],7) = @Date
)
End

ELSE IF @ComponentName = 'icmbType2'
Begin
SELECT @sReturnValue= (SELECT Case
When icmbType2Limit - icmbType2ProvisionedInUse <= tbl_Management.Treshold1 and icmbType2Limit - icmbType2ProvisionedInUse > tbl_Management.Treshold2
Then 'Yellow'
When icmbType2Limit - icmbType2ProvisionedInUse <= tbl_Management.Treshold2
THEN 'Red'
else ''
End as Available
FROM tbl_Complex INNER JOIN
tbl_Data1 ON tbl_Complex.ComplexID = tbl_Data1.ComplexID INNER JOIN
tbl_Management ON tbl_Data1.ComplexID = tbl_Management.ComplexID INNER JOIN
tbl_Component ON tbl_Management.ComponentID = tbl_Component.ComponentID
where tbl_Complex.ComplexName = @Complex and tbl_Component.ComponentName = @ComponentName and Left(tbl_Data1.[Date],7) = @Date
)
End

ELSE IF @ComponentName = 'icmbType3'
Begin
SELECT @sReturnValue= (SELECT Case
When icmbType3Limit - icmbType3ProvisionedInUse <= tbl_Management.Treshold1 and icmbType3Limit - icmbType3ProvisionedInUse > tbl_Management.Treshold2
Then 'Yellow'
When icmbType3Limit - icmbType3ProvisionedInUse <= tbl_Management.Treshold2
THEN 'Red'
else ''
End as Available
FROM tbl_Complex INNER JOIN
tbl_Data1 ON tbl_Complex.ComplexID = tbl_Data1.ComplexID INNER JOIN
tbl_Management ON tbl_Data1.ComplexID = tbl_Management.ComplexID INNER JOIN
tbl_Component ON tbl_Management.ComponentID = tbl_Component.ComponentID
where tbl_Complex.ComplexName = @Complex and tbl_Component.ComponentName = @ComponentName and Left(tbl_Data1.[Date],7) = @Date
)
End

ELSE IF @ComponentName = 'MS Server'
Begin
SELECT @sReturnValue= (SELECT Case
When (HighTUI - voipSessions) * 100 <= tbl_Management.Treshold1 and (HighTUI - voipSessions) * 100 > tbl_Management.Treshold2
Then 'Yellow'
When (HighTUI - voipSessions) * 100 <= tbl_Management.Treshold2
THEN 'Red'
else ''
End as Available
FROM tbl_Complex INNER JOIN
tbl_Data1 ON tbl_Complex.ComplexID = tbl_Data1.ComplexID INNER JOIN
tbl_Management ON tbl_Data1.ComplexID = tbl_Management.ComplexID INNER JOIN
tbl_Component ON tbl_Management.ComponentID = tbl_Component.ComponentID
where tbl_Complex.ComplexName = @Complex and tbl_Component.ComponentName = @ComponentName and Left(tbl_Data1.[Date],7) = @Date
)
End

ELSE IF @ComponentName = 'TS Server'
Begin
SELECT @sReturnValue= (SELECT Case
When (HighTUI - voipSessions) * 100 <= tbl_Management.Treshold1 and (HighTUI - voipSessions) * 100 > tbl_Management.Treshold2
Then 'Yellow'
When (HighTUI - voipSessions) * 100 <= tbl_Management.Treshold2
THEN 'Red'
else ''
End as Available
FROM tbl_Complex INNER JOIN
tbl_Data1 ON tbl_Complex.ComplexID = tbl_Data1.ComplexID INNER JOIN
tbl_Management ON tbl_Data1.ComplexID = tbl_Management.ComplexID INNER JOIN
tbl_Component ON tbl_Management.ComponentID = tbl_Component.ComponentID
where tbl_Complex.ComplexName = @Complex and tbl_Component.ComponentName = @ComponentName and Left(tbl_Data1.[Date],7) = @Date
)
End
RETURN @sReturnValue

END


Again visakh16, Thanks a lot! :-)
Go to Top of Page

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-10-04 : 13:59:58
Still don't see how that works. You set @sReturnValue = 'Red', then RETURN @sReturnValue, it should error, because RETURN can only return an INTEGER value.
Perhaps an OUTPUT variable would be better.








How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Go to Top of Page

infodemers
Posting Yak Master

183 Posts

Posted - 2012-10-04 : 14:19:06
No error, I declare the @sReturnValue as varchar(6) at the beginning, so RETURN @sReturnValue can return the string.
Go to Top of Page
   

- Advertisement -