| Author |
 Topic  |
|
|
infodemers
Posting Yak Master
Canada
166 Posts |
 Posted - 10/03/2012 : 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'. |
Edited by - infodemers on 10/03/2012 16:01:30
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 10/03/2012 : 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/
|
 |
|
|
infodemers
Posting Yak Master
Canada
166 Posts |
Posted - 10/04/2012 : 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 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 10/04/2012 : 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/
|
|
|
|
infodemers
Posting Yak Master
Canada
166 Posts |
Posted - 10/04/2012 : 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);
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 10/04/2012 : 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/
|
|
|
|
infodemers
Posting Yak Master
Canada
166 Posts |
Posted - 10/04/2012 : 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! :-) |
|
|
|
DonAtWork
Flowing Fount of Yak Knowledge
2111 Posts |
|
|
infodemers
Posting Yak Master
Canada
166 Posts |
Posted - 10/04/2012 : 14:19:06
|
| 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 |
|
|
| |
Topic |
|
|
|
Select @ReturnValue = exec ('select...
