MSSQL stored procedure selects all columns
I have a stored procedure where the SELECT statement looks like this:
SELECT @KeyName FROM dbo.FiConfig WITH (NOLOCK) WHERE IssuerKey = @IssuerKey
Where I create it like this:
CREATE PROCEDURE GET_FICONFIG
@IssuerKey INT,
@KeyName NVARCHAR(100)
Where KeyName
is the name of the column from which I am retrieving my data.
My question is, is it possible to pass *
to select all columns even though the procedure is asking for a specific column name?
Edit: Unfortunately, I think I may have phrased my question incorrectly. I was looking for a way to check if my stored procedure can determine if I want to select all records from a table or only specific columns from a record. I solved my problem by using some NULL checks and if / else statements.
source to share
You can use a dynamic query:
CREATE PROCEDURE GET_FICONFIG
@IssuerKey INT,
@KeyName NVARCHAR(100)
AS
declare @s varchar(500) = 'SELECT ' + @KeyName + ' FROM dbo.FiConfig WITH (NOLOCK) WHERE IssuerKey = ' + CAST(@IssuerKey as VARCHAR(10))
exec(@s)
You must be careful. Possibly Sql Injection
. But you cannot pass column names as parameters for a dynamic query, so there is no other way.
But you can choose from the table INFORMATION_SCHEMA.COLUMNS
to make sure a valid column name passed:
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'FiConfig' AND COLUMN_NAME = @KeyName)
BEGIN
declare @s varchar(500) = 'SELECT ' + @KeyName + ' FROM dbo.FiConfig WITH (NOLOCK) WHERE IssuerKey = ' + CAST(@IssuerKey as VARCHAR(10))
exec(@s)
END
ELSE
THROW...
EDIT:
As @Lamak said it is better to use a function QUOTENAME
. it will automatically add parentheses around the variable value:
CREATE PROCEDURE GET_FICONFIG
@IssuerKey INT,
@KeyName NVARCHAR(100)
AS
declare @s varchar(500) = 'SELECT ' + QUOTENAME(@KeyName) + ' FROM dbo.FiConfig WITH (NOLOCK) WHERE IssuerKey = ' + CAST(@IssuerKey as VARCHAR(10))
exec(@s)
source to share
Try something like this:
DECLARE @SqlCommand nvarchar(max)
SELECT @SqlCommand = 'SELECT ' + @keyname + ' FROM dbo.FiConfig WITH (NOLOCK) WHERE IssuerKey = ' + cast(@IssuerKey as varchar(10))
EXEC sp_executeSQL @SqlCommand
This will allow you to pass in a column name or an arbitrary expression (for example, *
or an aggregate function for example sum()
)
Keep in mind that as it is worth it, it is dangerous as there is no confirmation in @keyname, so it is prone to SQL injection if someone goes over like a string ''; DELETE FROM USERS; --
.
However, you can parameterize sp_executesql
to reduce this; the documentation for this stored procedure is here:
https://msdn.microsoft.com/en-gb/library/ms188001.aspx
Parameterization sp_executesql
, however, would prevent you from going in '*'
as a stored procedure argument ...
source to share