March 13, 2014 at 10:16 am
I am creating a function where I want to pass it parameters and then use those parameters in a select statement. When I do that it selects the variable name as a literal not a column. How do I switch that context.
Query:
ALTER FUNCTION [dbo].[ufn_Banner_Orion_Employee_Comparison_parser_v2]
(
@BANNER_COLUMN AS VARCHAR(MAX),
@ORION_COLUMN AS VARCHAR(MAX)
)
RETURNS @Banner_Orion_Employee_Comparison TABLE
(
LAST_NAME nvarchar(max),
EMPNO int,
BannerColumnName nvarchar(max),
BANNER nvarchar(max),
ORION nvarchar(max)
)
AS
BEGIN
INSERT INTO @Banner_Orion_Employee_Comparison
(LAST_NAME, BANNER, ORION)
SELECT
a.LAST_NAME, @BANNER_COLUMN, @ORION_COLUMN
FROM OPENQUERY(ORCLPROD_APDORACLE, 'select LAST_NAME, BANNER_RANK, BADGE, EMP_STATUS from XTRACT_VIEW') AS a
inner join IWM_Stage.dbo.ViewPersonnel AS b
on a.BADGE = b.badge
WHERE a.EMP_STATUS = 'A'
and a.BANNER_RANK <> b.[rank]
RETURN;
END;
GO
Returns:
I execute this:
select * from ufn_Banner_Orion_Employee_Comparison_parser_v2 ('a.BANNER_RANK' , 'b.[rank]')
and get:
CerecerezNULLNULLa.BANNER_RANKb.[rank]
March 13, 2014 at 10:50 am
You have parameters that you're passing values to and then those parameters are used in your SELECT statement to return the values passed. That's how parameters work. What you're looking to do is create an ad hoc query and use the parameters as part of the string building. You'd need to build a string and use the parameters as values
SET @MyString = 'SELECT A, ' + @MyParam1 + ', ' + @myparam2 + ' FROM Something WHERE X = 42;'
Then you can execute the @MyString command. But, this is a dangerous thing that can lead to SQL Injection attacks.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
March 13, 2014 at 11:10 am
I have already tried that. I just reset the statement to that and got this:
Internal sitting on a server behind a DMZ, so no worries about sql injection.
Msg 203, Level 16, State 2, Line 1
The name 'INSERT INTO @Banner_Orion_Employee_Comparison
(LAST_NAME, BANNER, ORION)
SELECT
a.LAST_NAME, @BANNER_COLUMN, @ORION_COLUMN
FROM OPENQUERY(ORCLPROD_APDORACLE, 'select LAST_NAME, BANNER_RANK, BADGE, EMP_STATUS from XTRACT_VIEW') AS a
inner join IWM_Stage.dbo.ViewPersonnel AS b
on a.BADGE = b.badge
WHERE a.EMP_STATUS = 'A'
and a.BANNER_RANK <> b.[rank]' is not a valid identifier.
March 13, 2014 at 11:15 am
You have to build the string. Just referencing the parameters won't work.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
March 13, 2014 at 11:17 am
I did that. Here is the full function (Thanks for your help by the way):
USE [CTM_Reporting]
GO
/****** Object: UserDefinedFunction [dbo].[ufn_Banner_Orion_Employee_Comparison_parser_v2] Script Date: 3/13/2014 11:57:36 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[ufn_Banner_Orion_Employee_Comparison_parser_v2]
(
@BANNER_COLUMN AS VARCHAR(MAX),
@ORION_COLUMN AS VARCHAR(MAX)
)
RETURNS @Banner_Orion_Employee_Comparison TABLE
(
LAST_NAME nvarchar(max),
EMPNO int,
BannerColumnName nvarchar(max),
BANNER nvarchar(max),
ORION nvarchar(max)
)
AS
BEGIN
DECLARE @MyString as NVARCHAR(MAX);
SET @MyString =
'INSERT INTO @Banner_Orion_Employee_Comparison
(LAST_NAME, BANNER, ORION)
SELECT
a.LAST_NAME, @BANNER_COLUMN, @ORION_COLUMN
FROM OPENQUERY(ORCLPROD_APDORACLE, ''select LAST_NAME, BANNER_RANK, BADGE, EMP_STATUS from XTRACT_VIEW'') AS a
inner join IWM_Stage.dbo.ViewPersonnel AS b
on a.BADGE = b.badge
WHERE a.EMP_STATUS = ''A''
and a.BANNER_RANK <> b.[rank]'
EXEC @MyString;
RETURN;
END;
GO
March 13, 2014 at 11:20 am
george.auckland 44813 (3/13/2014)
I did that. Here is the full function (Thanks for your help by the way):USE [CTM_Reporting]
GO
/****** Object: UserDefinedFunction [dbo].[ufn_Banner_Orion_Employee_Comparison_parser_v2] Script Date: 3/13/2014 11:57:36 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[ufn_Banner_Orion_Employee_Comparison_parser_v2]
(
@BANNER_COLUMN AS VARCHAR(MAX),
@ORION_COLUMN AS VARCHAR(MAX)
)
RETURNS @Banner_Orion_Employee_Comparison TABLE
(
LAST_NAME nvarchar(max),
EMPNO int,
BannerColumnName nvarchar(max),
BANNER nvarchar(max),
ORION nvarchar(max)
)
AS
BEGIN
DECLARE @MyString as NVARCHAR(MAX);
SET @MyString =
'INSERT INTO @Banner_Orion_Employee_Comparison
(LAST_NAME, BANNER, ORION)
SELECT
a.LAST_NAME, ' + @BANNER_COLUMN + ', ' + @ORION_COLUMN + '
FROM OPENQUERY(ORCLPROD_APDORACLE, ''select LAST_NAME, BANNER_RANK, BADGE, EMP_STATUS from XTRACT_VIEW'') AS a
inner join IWM_Stage.dbo.ViewPersonnel AS b
on a.BADGE = b.badge
WHERE a.EMP_STATUS = ''A''
and a.BANNER_RANK <> b.[rank]'
EXEC @MyString;
RETURN;
END;
GO
Like that.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
March 13, 2014 at 11:30 am
I just copied that SQL into management studio. and got the same result:
Msg 203, Level 16, State 2, Line 1
The name 'INSERT INTO @Banner_Orion_Employee_Comparison
(LAST_NAME, BANNER, ORION)
SELECT
a.LAST_NAME, a.BANNER_COLUMN, b.[rank]
FROM OPENQUERY(ORCLPROD_APDORACLE, 'select LAST_NAME, BANNER_RANK, BADGE, EMP_STATUS from XTRACT_VIEW') AS a
inner join IWM_Stage.dbo.ViewPersonnel AS b
on a.BADGE = b.badge
WHERE a.EMP_STATUS = 'A'
and a.BANNER_RANK <> b.[rank]' is not a valid identifier.
---------------------------
@ORION_COLUMN AS VARCHAR(MAX)
)
RETURNS @Banner_Orion_Employee_Comparison TABLE
(
LAST_NAME nvarchar(max),
EMPNO int,
BannerColumnName nvarchar(max),
BANNER nvarchar(max),
ORION nvarchar(max)
)
AS
BEGIN
DECLARE @MyString as NVARCHAR(MAX);
SET @MyString =
'INSERT INTO @Banner_Orion_Employee_Comparison
(LAST_NAME, BANNER, ORION)
SELECT
a.LAST_NAME, ' + @BANNER_COLUMN + ', ' + @ORION_COLUMN + '
FROM OPENQUERY(ORCLPROD_APDORACLE, ''select LAST_NAME, BANNER_RANK, BADGE, EMP_STATUS from XTRACT_VIEW'') AS a
inner join IWM_Stage.dbo.ViewPersonnel AS b
on a.BADGE = b.badge
WHERE a.EMP_STATUS = ''A''
and a.BANNER_RANK <> b.[rank]'
EXEC @MyString;
RETURN;
END;
March 13, 2014 at 11:43 am
You don't have b.Rank as one of the columns defined in the SELECT list that you're selecting from.
This is not a good practice, for some of the reasons you're hitting now, let alone SQL Injection.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply