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.
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)ASBEGIN-- Declare the variables hereDECLARE @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.Treshold2Then ''' + @Threshold1 + ''' When ' + @ComponentName + 'Limit - ' + @ComponentName + 'ProvisionedInUse <= tbl_Management.Treshold2THEN ''' + @Threshold2 + ''' else ''' + @ok + '''End as Available, tbl_Data1.[Date]FROM tbl_Complex INNER JOINtbl_Data1 ON tbl_Complex.ComplexID = tbl_Data1.ComplexID INNER JOINtbl_Management ON tbl_Data1.ComplexID = tbl_Management.ComplexID INNER JOINtbl_Component ON tbl_Management.ComponentID = tbl_Component.ComponentIDwhere 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 @ReturnValueENDI 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 belowAlso if you want to return values through variable in dynamic sql, you need to use sp_executesql rather than EXECseehttp://oakdome.com/programming/SQL_DynamicSQL_sp_executesql.php------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
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_Data1Then how could I use that Sp from within another query?Within a stored procedure, I got it running as follow:How could I useALTER PROCEDURE[dbo].[sp_Treshold]@Complex NvarChar(12),@ComponentName NvarChar(12),@Date varchar(7)ASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements.SET NOCOUNT ON; -- Insert statements for procedure hereDECLARE @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.Treshold2Then ''' + @Threshold1 + ''' When ' + @ComponentName + 'Limit - ' + @ComponentName + 'ProvisionedInUse <= tbl_Management.Treshold2THEN ''' + @Threshold2 + ''' else ''' + @ok + '''End as Available, tbl_Data1.[Date]FROM tbl_Complex INNER JOINtbl_Data1 ON tbl_Complex.ComplexID = tbl_Data1.ComplexID INNER JOINtbl_Management ON tbl_Data1.ComplexID = tbl_Management.ComplexID INNER JOINtbl_Component ON tbl_Management.ComponentID = tbl_Component.ComponentIDwhere 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 @SQLStringEND |
|
|
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_Data1Then how could I use that Sp from within another query?Within a stored procedure, I got it running as follow:How could I useALTER PROCEDURE[dbo].[sp_Treshold]@Complex NvarChar(12),@ComponentName NvarChar(12),@Date varchar(7)ASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements.SET NOCOUNT ON; -- Insert statements for procedure hereDECLARE @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.Treshold2Then ''' + @Threshold1 + ''' When ' + @ComponentName + 'Limit - ' + @ComponentName + 'ProvisionedInUse <= tbl_Management.Treshold2THEN ''' + @Threshold2 + ''' else ''' + @ok + '''End as Available, tbl_Data1.[Date]FROM tbl_Complex INNER JOINtbl_Data1 ON tbl_Complex.ComplexID = tbl_Data1.ComplexID INNER JOINtbl_Management ON tbl_Data1.ComplexID = tbl_Management.ComplexID INNER JOINtbl_Component ON tbl_Management.ComponentID = tbl_Component.ComponentIDwhere 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 @SQLStringEND
whats the purpose of dynamic code inside? why are your column names not static?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
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 ICMBType3Then 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); |
|
|
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 ICMBType3Then 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 MVPhttp://visakhm.blogspot.com/ |
|
|
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...etcAlter FUNCTION [dbo].[fn_getTresholdColor](@Complex NvarChar(12),@ComponentName NvarChar(12), @Date varchar(7)) RETURNS varchar(6)ASBEGINDeclare @sReturnValue varchar(6);If @ComponentName = 'vm'Begin SELECT @sReturnValue= (SELECT Case When vmLimit - vmProvisionedInUse <= tbl_Management.Treshold1 and vmLimit - vmProvisionedInUse > tbl_Management.Treshold2Then 'Yellow' When vmLimit - vmProvisionedInUse <= tbl_Management.Treshold2THEN 'Red' else ''End as AvailableFROM tbl_Complex INNER JOINtbl_Data1 ON tbl_Complex.ComplexID = tbl_Data1.ComplexID INNER JOINtbl_Management ON tbl_Data1.ComplexID = tbl_Management.ComplexID INNER JOINtbl_Component ON tbl_Management.ComponentID = tbl_Component.ComponentIDwhere tbl_Complex.ComplexName = @Complex and tbl_Component.ComponentName = @ComponentName and Left(tbl_Data1.[Date],7) = @Date )EndELSE IF @ComponentName = 'vvm'BeginSELECT @sReturnValue= (SELECT Case When vvmLimit - vvmProvisionedInUse <= tbl_Management.Treshold1 and vvmLimit - vvmProvisionedInUse > tbl_Management.Treshold2Then 'Yellow' When vvmLimit - vvmProvisionedInUse <= tbl_Management.Treshold2THEN 'Red' else ''End as AvailableFROM tbl_Complex INNER JOINtbl_Data1 ON tbl_Complex.ComplexID = tbl_Data1.ComplexID INNER JOINtbl_Management ON tbl_Data1.ComplexID = tbl_Management.ComplexID INNER JOINtbl_Component ON tbl_Management.ComponentID = tbl_Component.ComponentIDwhere tbl_Complex.ComplexName = @Complex and tbl_Component.ComponentName = @ComponentName and Left(tbl_Data1.[Date],7) = @Date )EndELSE IF @ComponentName = 'icmbType1'BeginSELECT @sReturnValue= (SELECT Case When icmbType1Limit - icmbType1ProvisionedInUse <= tbl_Management.Treshold1 and icmbType1Limit - icmbType1ProvisionedInUse > tbl_Management.Treshold2Then 'Yellow' When icmbType1Limit - icmbType1ProvisionedInUse <= tbl_Management.Treshold2THEN 'Red' else ''End as AvailableFROM tbl_Complex INNER JOINtbl_Data1 ON tbl_Complex.ComplexID = tbl_Data1.ComplexID INNER JOINtbl_Management ON tbl_Data1.ComplexID = tbl_Management.ComplexID INNER JOINtbl_Component ON tbl_Management.ComponentID = tbl_Component.ComponentIDwhere tbl_Complex.ComplexName = @Complex and tbl_Component.ComponentName = @ComponentName and Left(tbl_Data1.[Date],7) = @Date )EndELSE IF @ComponentName = 'icmbType2'BeginSELECT @sReturnValue= (SELECT Case When icmbType2Limit - icmbType2ProvisionedInUse <= tbl_Management.Treshold1 and icmbType2Limit - icmbType2ProvisionedInUse > tbl_Management.Treshold2Then 'Yellow' When icmbType2Limit - icmbType2ProvisionedInUse <= tbl_Management.Treshold2THEN 'Red' else ''End as AvailableFROM tbl_Complex INNER JOINtbl_Data1 ON tbl_Complex.ComplexID = tbl_Data1.ComplexID INNER JOINtbl_Management ON tbl_Data1.ComplexID = tbl_Management.ComplexID INNER JOINtbl_Component ON tbl_Management.ComponentID = tbl_Component.ComponentIDwhere tbl_Complex.ComplexName = @Complex and tbl_Component.ComponentName = @ComponentName and Left(tbl_Data1.[Date],7) = @Date )EndELSE IF @ComponentName = 'icmbType3'BeginSELECT @sReturnValue= (SELECT Case When icmbType3Limit - icmbType3ProvisionedInUse <= tbl_Management.Treshold1 and icmbType3Limit - icmbType3ProvisionedInUse > tbl_Management.Treshold2Then 'Yellow' When icmbType3Limit - icmbType3ProvisionedInUse <= tbl_Management.Treshold2THEN 'Red' else ''End as AvailableFROM tbl_Complex INNER JOINtbl_Data1 ON tbl_Complex.ComplexID = tbl_Data1.ComplexID INNER JOINtbl_Management ON tbl_Data1.ComplexID = tbl_Management.ComplexID INNER JOINtbl_Component ON tbl_Management.ComponentID = tbl_Component.ComponentIDwhere tbl_Complex.ComplexName = @Complex and tbl_Component.ComponentName = @ComponentName and Left(tbl_Data1.[Date],7) = @Date )EndELSE IF @ComponentName = 'MS Server'BeginSELECT @sReturnValue= (SELECT Case When (HighTUI - voipSessions) * 100 <= tbl_Management.Treshold1 and (HighTUI - voipSessions) * 100 > tbl_Management.Treshold2Then 'Yellow' When (HighTUI - voipSessions) * 100 <= tbl_Management.Treshold2THEN 'Red' else ''End as AvailableFROM tbl_Complex INNER JOINtbl_Data1 ON tbl_Complex.ComplexID = tbl_Data1.ComplexID INNER JOINtbl_Management ON tbl_Data1.ComplexID = tbl_Management.ComplexID INNER JOINtbl_Component ON tbl_Management.ComponentID = tbl_Component.ComponentIDwhere tbl_Complex.ComplexName = @Complex and tbl_Component.ComponentName = @ComponentName and Left(tbl_Data1.[Date],7) = @Date )EndELSE IF @ComponentName = 'TS Server'BeginSELECT @sReturnValue= (SELECT Case When (HighTUI - voipSessions) * 100 <= tbl_Management.Treshold1 and (HighTUI - voipSessions) * 100 > tbl_Management.Treshold2Then 'Yellow' When (HighTUI - voipSessions) * 100 <= tbl_Management.Treshold2THEN 'Red' else ''End as AvailableFROM tbl_Complex INNER JOINtbl_Data1 ON tbl_Complex.ComplexID = tbl_Data1.ComplexID INNER JOINtbl_Management ON tbl_Data1.ComplexID = tbl_Management.ComplexID INNER JOINtbl_Component ON tbl_Management.ComponentID = tbl_Component.ComponentIDwhere tbl_Complex.ComplexName = @Complex and tbl_Component.ComponentName = @ComponentName and Left(tbl_Data1.[Date],7) = @Date )EndRETURN @sReturnValue END Again visakh16, Thanks a lot! :-) |
|
|
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 |
|
|
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. |
|
|
|
|
|
|
|