0

I have a 'conceptual' question. I understand how to create a simple stored procedure with parameters.

My question now is, how do I write a stored procedure such that the columns I want to SELECT ... FROM are dynamic.

Example run 1: I want to run a stored procedure by only 

SELECT firstName, LastName

Example run 2: I want to run a stored procedure by only 

SELECT firstName, LastName, Department

The columns I want to SELECT list each time I run the stored procedure can change.

I have an existing stored procedure (_SelectFromQry) written like this: 

ALTER PROCEDURE [dbo].[_SelectFromQry] 
    @queryname NVARCHAR(255)
AS
BEGIN
    SELECT TBLNAME, TBLCOL, TBLCOLLABEL, POSITION
    FROM QRY
    WHERE QUERYNAME = @queryname
END

The result of the above procedure is the 'dynamic columns' that I want to SELECT from another table.

I have no clear idea how to write a stored procedure which SELECT FROM another table according to the results of '_SelectFromQry'. Would it involve creating some sort of 'list parameter' where I would SELECT FROM @LIST ?

Appreciate any kind of help.

Improve this question
1
  • Either through IF-ELSE or through Dynamic SQL Commented May 22, 2018 at 4:36

2 Answers 2

Reset to default
2

There is simple conceptual answer for your conceptual question: don't do this, at least for traditional SQL Server. Such dynamism breaks the internal mechanisms of the query execution optimization based on the execution plans and statistics.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Disagree with the "DON'T EVER DO THAT". All languages provide features that enable you to cope with sometimes complex situations. Dynamic SQL is a last resort in some cases, and it saves you from implementing an unreadable series of IF-THEN-ELSEs. So, YES it can be used but WISELY.
I agree with this answer. There are definitely some situations where dynamic SQL is useful and desirable. This is not one of those situations. There are better ways to filter the columns that someone wants to use -- such as simply using a table valued function or view and selecting the desired columns at the caller level. Confusing stored procedures for views and functions is just sloppiness or ignorance.
1

I am thinking there may be simple ways to do this, such as

ALTER PROCEDURE [dbo].[_SelectFromQry] @columns nvarchar(MAX), @filtercolumn1 NVARCHAR(10)
AS
BEGIN
    DECLARE @query NVARCHAR (MAX)

    SET @query = N'SELECT '+@columns+' FROM TABLE 
    WHERE column1 = '+@filtercolumn1

    EXECUTE sp_executesql @query
END

To execute

EXEC _SelectFromQry 'Column1,Column2,...', 'A'
Improve this answer

3 Comments

You have an error. To execute the SELECT string it should be EXECUTE sp_executesql @query.
May I know if the table name, can also be a parameter?
@jinchengteo yes. the whole SELECT statement can be part of the string. In fact, this method allows you to perform almost any operation that can be coded in SQL, including a whole anonymous block (i.e. BEGIN....END). As such, and following my comment to the solution posted by olk, be careful what you put in there.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.