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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Select @ReturnValue = exec ('select...
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

infodemers
Posting Yak Master

Canada
183 Posts

Posted - 10/03/2012 :  15:49:31  Show Profile  Reply with Quote
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'.

Edited by - infodemers on 10/03/2012 16:01:30

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 10/03/2012 :  17:25:21  Show Profile  Reply with Quote
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

Canada
183 Posts

Posted - 10/04/2012 :  08:39:27  Show Profile  Reply with Quote
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

India
52326 Posts

Posted - 10/04/2012 :  10:32:03  Show Profile  Reply with Quote
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

Canada
183 Posts

Posted - 10/04/2012 :  11:14:06  Show Profile  Reply with Quote
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

India
52326 Posts

Posted - 10/04/2012 :  12:01:40  Show Profile  Reply with Quote
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

Canada
183 Posts

Posted - 10/04/2012 :  13:31:23  Show Profile  Reply with Quote

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
Flowing Fount of Yak Knowledge

2167 Posts

Posted - 10/04/2012 :  13:59:58  Show Profile  Reply with Quote
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

Canada
183 Posts

Posted - 10/04/2012 :  14:19:06  Show Profile  Reply with Quote
No error, I declare the @sReturnValue as varchar(6) at the beginning, so RETURN @sReturnValue can return the string.

Edited by - infodemers on 10/04/2012 15:47:30
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000