SQL Server Forums
Profile | Register | 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...
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

infodemers
Posting Yak Master

Canada
181 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
52317 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
181 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
52317 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
181 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
52317 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
181 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

2161 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
181 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  
 New 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.09 seconds. Powered By: Snitz Forums 2000