I Need to Pass Column Names Using Variable in Select Statement in Store Procedure But I Cannot Use Dynamic Query

Can I pass a column name to a substring function using a variable?

No, you cannot do it that way. SQL normally has to know what columns you are working with for optimization. One way to do it would be to Have each column name in the where clause:

DELETE TOP (@Dimension) 
FROM  [dbo].[TestDelete]
WHERE (@ColumnName = 'DateReceived' and SUBSTRING(DateReceived, 1, 2) = @Year)
   OR (@ColumnName = 'DateSent' and SUBSTRING(DateSent, 1, 2) = @Year)

Another way would be to use sp_executesql and create the delete as a string:

declare @sql nvarchar(max) = concat('DELETE TOP(@Dimension) ',
    'FROM [dbo].[TestDelete] ',
    'WHERE SUBSTRING(', @ColumnName, ', 1, 2) = @Year');
exec sp_executesql @sql,
    N'@Dimension int', N'Year int',
    @Dimension = @Dimension, @Year = @Year;

I don't think either is a good idea though. Why do you need the column name to be passed?

Can I pass variable to select statement as column name in SQL Server

You can't use variable names to bind columns or other system objects, you need dynamic sql

DECLARE @value varchar(10)  
SET @value = 'intStep'  
DECLARE @sqlText nvarchar(1000); 

SET @sqlText = N'SELECT ' + @value + ' FROM dbo.tblBatchDetail'
Exec (@sqlText)

How can I turn this dynamic query into a procedure or function?

You can create scalar user defined function, which returns the sql statement.

CREATE FUNCTION dbo.udf_GenerateSelectQuery() 
Returns nvarchar(max)
AS
BEGIN
declare @t table( tablename SYSNAME) 
declare @sql Nvarchar(max)

set @sql = ''
insert into @t
SELECT t.TABLE_NAME AS table_name FROM INFORMATION_SCHEMA.TABLES AS t

SELECT @sql = @sql + 'Select convert(varchar(5),svc_defs.svc_id) as svcid, data_id, crid, d_custid, d_active From ' + tablename + 
' inner join svc_defs on svc_defs.svc_table = ' + '''' + tablename + '''' + ' union ' from @t 

--remove the trailing 'union'
Select @sql = substring(@sql, 1, len(@sql) - 6)

RETURN @sql
END

you can call it as

declare @sqlstmt NVARCHAR(max) =  dbo.udf_GenerateSelectQuery()
SELECT @sqlstmt 

or as

declare @sqlstmt NVARCHAR(max) 
SET @sqlstmt = (SELECT dbo.udf_GenerateSelectQuery())
SELECT @sqlstmt

How to write dynamic query with % string compare?

Use string concatenation:

DECLARE
  x   VARCHAR2(20);
  cur SYS_REFCURSOR;
BEGIN
  x := 'A';

  OPEN cur FOR
    select *
    from   table_name
    where  category like x || '%SITE';

  -- Do something with the cursor.
END;
/

db<>fiddle here

---

Regarding you comment:

EXECUTE IMMEDIATE 'select * from table_name where category like x || '%SITE''

You need to escape the quotes by doubling them up and you probably also want to use a bind variable (to prevent SQL injection attacks):

EXECUTE IMMEDIATE
  'select * from table_name where category like :1 || ''%SITE'''
  USING x;

However, there is nothing in that query that requires dynamic SQL and you can do exactly the same without dynamic SQL if you either use a cursor (as per my example above) or use SELECT ... [BULK COLLECT] INTO.

Passing Int to dynamic stored procedure fails

The + only works with strings. If you use a number TSQL assumes you are trying to use the addition operator, and tries to convert the string argument to int.

eg this

select 1 + '2'

works and returns 3.

Use CONCAT instead of +, or use an explicit conversion on the int.

eg

WHERE survey_id = ' + cast(@SurveyID as varchar(20)) + '

SQL Stored Procedure with Dynamic Column Names

You can wrap your query in a procedure just as it is. Then you can execute it from from your app/web and get a DataTable as the result. When you bind the DataTable to the DataGrid it should automatically render the columns in the DataGrid

CREATE PROCEDURE GetDynamicReport

     @StartDate as Date
    ,@EndDate as Date
    ,@DepartmentID as Varchar(10)
    ,@ColumnFilter as Varchar(3)
AS
BEGIN
    DECLARE @ColumnList AS Varchar(MAX)

    SELECT @ColumnList =  COALESCE(@ColumnList, ',') + c.name+',' FROM sys.columns c
    WHERE c.object_id = OBJECT_ID('tblDetails') AND c.Name LIKE @ColumnFilter + '%'

    SET @ColumnList = Left(@ColumnList,Len(@ColumnList)-1)

    DECLARE @Template AS Varchar(max)
    SET @Template = 'SELECT [RecordID]
          ,[DateRecord]
          ,[DepartmentID]
          ,[Shift]
          ,[ShiftLength]
          ,[ShiftType]
          ,[Active]
          ,[Comment]
          {ColumnList}
      FROM [dbo].[tblDetails]
      WHERE DateRecord >= ''{StartDate}'' AND DateRecord <= ''{EndDate}'' AND DepartmentID = ''{DepartmentID}''
      ORDER BY DateRecord'

    SET @Template = REPLACE(@Template, '{ColumnList}', @ColumnList) 
    SET @Template = REPLACE(@Template, '{StartDate}', @StartDate) 
    SET @Template = REPLACE(@Template, '{EndDate}', @EndDate) 
    SET @Template = REPLACE(@Template, '{DepartmentID}', @DepartmentID ) 

    EXEC (@Template);

END

GO
-- Execute it like this
EXEC dbo.GetDynamicReport 
    @StartDate = '2015-06-03 06:38:07',
    @EndDate = '2015-06-03 06:38:07',
    @DepartmentID = 'abc',
    @ColumnFilter = 'GM'

Calling the procedure

public static DataTable CallReportProcedure(string connectionString, DateTime startDate, DateTime endDate, string departmentID, string columnFilter)
{
    using(var conn = new SqlConnection(connectionString))
    using(var cmd = new SqlCommand("GetDynamicReport", conn) 
        { CommandType = System.Data.CommandType.StoredProcedure} )
    {
        cmd.Parameters.AddWithValue("@StartDate", startDate);
        cmd.Parameters.AddWithValue("@EndDate", endDate);
        cmd.Parameters.AddWithValue("@DepartmentID", departmentID);
        cmd.Parameters.AddWithValue("@ColumnFilter", columnFilter);

        var da = new SqlDataAdapter(cmd);
        var ds = new DataSet();
        da.Fill(ds);
        return ds.Tables[0];
    }
}

Then if you really need to know information about the columns you can inspect the resulting DataTable

static void Main(string[] args)
        {
            SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
            builder.DataSource = "localhost";
            builder.InitialCatalog = "peter";
            builder.IntegratedSecurity = true;
            var connectionString = builder.ConnectionString;

            var resultTable = p.CallReportProcedure(connectionString, new DateTime(2015, 1, 1), new DateTime(2015, 5, 1), "GMC", "GM");
            // Bind the resultTable to your DataGrid

            // If you need to know the column names then you can loop through the Columns of the resultTable
            foreach (DataColumn col in resultTable.Columns)
            {
                // Print the names of the columns from the result
                Console.WriteLine(col.ColumnName);
            }
        }

SQL Stored Procedure with column name as variable

It is better to use dymamic SQL as Andrew or Roman Czerwinski recommended.
But if you don't want to use dynamic SQL then you can use this logic:

ALTER PROC [dbo].[GetCard](
    @Input varchar(50),
    @Input2 varchar(50)
)
AS
SELECT * 
FROM Main 
WHERE CardName  = case when @Input = 'CardName'    then @Input2 else CardName    end
and OtherColumn = case when @Input = 'OtherColumn' then @Input2 else OtherColumn end
--etc...

---

Related Topics