SQL Server – Stored Procedure with Dynamic Variables

dynamic-sqlsql serverstored-procedurest-sql

I am attempting to build a stored procedure with variables that will change over time. on SP execution (Right clicking SP in programmability under the database and execute) results in

Error output:

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'file20160303FROMx:'.

(1 row(s) affected)

I am really open to any feedback, be it a complete re-design if need be. Still a junior in this field.

Script:

USE [dbname]
GO
/****** Object:  StoredProcedure [dbo].[procname]    Script Date: 2016-03-03 09:45:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


 --=============================================
 --Author:      <Author,,Name>
 --Create date: <Create Date,,>
 --Description: <Description,,>
 --=============================================
 ALTER PROC [dbo].[procname]
@p1 nvarchar(8)
AS
BEGIN

SET NOCOUNT ON;
Declare @insertvariable NVARCHAR(MAX)
Declare @var1 NVARCHAR (32) = 'x:\xxxx\xxxx\file\err';
Declare @var2 NVARCHAR (15) = convert(varchar, getdate(),112) + '.' + replace(convert(varchar, getdate(),108),':','')
Declare @var3 NVARCHAR (MAX) = @var1 + @var2 + '.log';
Declare @var4  NVARCHAR(MAX)= 'x:\xxxx\xxxx\file';
Declare @var5 NVARCHAR(MAX) = @var2 + @p1 + '.tab.txt';
Declare @formatFile NVARCHAR (MAX) = 'x:\xxxx\xxxx\formatFile';

set @insertvariable =  N'
BULK INSERT dbname.dbo.tablename' + @p1 + 'FROM' + @var5 + 'WITH 
    (
   FIRSTROW = 1,
   ORDER (Id),
   CODEPAGE = Finnish_Swedish_CI_AS,
   FIELDTERMINATOR = \t,
   ROWTERMINATOR = 0x0a,
   TABLOCK,
   ERRORFILE = @var3,
   FORMATFILE = @formatFile, 
   KEEPNULLS
   );'
   exec sp_executesql @insertvariable, N'@p1 nvarchar, @var5 nvarchar, @var3 nvarchar, @formatFile nvarchar', @p1, @var5, @var3, @formatFile 
   end

Best Answer

You are missing spaces in your concatenated string and you also need to quote your filename. I also don't see the point of mixing concatenation and calling sp_executesql with parameters.

Try changing this part:

set @insertvariable =  N'
BULK INSERT dbname.dbo.tablename' + @p1 + 'FROM' + @var5 + 'WITH 
    (
   FIRSTROW = 1,
   ORDER (Id),
   CODEPAGE = Finnish_Swedish_CI_AS,
   FIELDTERMINATOR = \t,
   ROWTERMINATOR = 0x0a,
   TABLOCK,
   ERRORFILE = @var3,
   FORMATFILE = @formatFile, 
   KEEPNULLS
   );'
   exec sp_executesql @insertvariable, N'@p1 nvarchar, @var5 nvarchar, @var3 nvarchar, @formatFile nvarchar', @p1, @var5, @var3, @formatFile 
   end

Into this, mind the spaces where you concatenate @p1 and @var5 and single quotes:

set @insertvariable =  N'
BULK INSERT dbname.dbo.tablename ' + @p1 + ' FROM ''' + @var5 + ''' WITH 
    (
   FIRSTROW = 1,
   ORDER (Id),
   CODEPAGE = ''Finnish_Swedish_CI_AS'',
   FIELDTERMINATOR = \t,
   ROWTERMINATOR = 0x0a,
   TABLOCK,
   ERRORFILE = ''' + @var3 + ''',
   FORMATFILE = ''' + @formatFile + ''', 
   KEEPNULLS
   );'
   exec sp_executesql @insertvariable
   end

Related Solutions

Sql-server – How to include a datetime parameter within a stored procedure along with string query

The fundamental issue is that TSQL cannot implicitly convert datetime (or integer or floating point) into character data types. It actually goes backwards, it attempts to implicitly convert the character data to the datetime (int/floating point value) based on the data type precedence rules. This is what your error message is telling you by the way, it couldn't convert the strings to datetime values. You have to explicitly ask it to mash values into character strings.

This example demonstrates the principal with integers as they are easier to understand expected values.

DECLARE 
    @stringInt nvarchar(3)
,   @intint int

SELECT
    @stringInt = N'3'
,   @intint = 5

SELECT 
    @stringInt + @intint AS implicit_conversion
,   @stringInt + CAST(@intint AS nvarchar(5)) AS explicit_conversion

The value of the implicit conversion shows the @stringint is converted to integer first and then the + is treated as numeric addition and results in 8. The explicit conversion of @intint to a character data type results in the + sign treated as concatenation with the returned string of 35

implicit_conversion explicit_conversion
------------------- -------------------
8                   35

To solve your supplied problem, you must explicitly cast your datetime values to a character type so the query string can be concatenated as expected.

set @query='SELECT [col1] 
FROM [Table1]              
WHERE ([col2] BETWEEN ''' + CONVERT(nvarchar(24), @FromDate, 121) +''' AND'''+ CONVERT(nvarchar(24), @ToDate, 121) +''')'

But as indicated above, you really don't want to do that for a number of reasons, SQL Injection being one of them. It also makes your maintenance much harder when you're slicing and dicing a query string in TSQL.

A better approach is to parameterize your query and use the power of sp_executesql. A nice thing about the sp_executesql parameter is that you don't have to use all the parameters supplied. Depending on what you are truly trying to do, this may be beneficial.

Sample table and data

CREATE TABLE 
dbo.table1
(col1 int, col2 datetime)

INSERT INTO
    dbo.table1
SELECT
3, '2009-04-06'
UNION ALL SELECT
1, '2001-09-11'

Demo of using parameters

DECLARE
    @FromDate datetime,
    @ToDate datetime

SELECT    
    @FromDate = '2005-03-17'
,   @ToDate = current_timestamp

DECLARE
    @query nvarchar(max)
SET @query = N'    
SELECT [col1] 
FROM [Table1]              
WHERE ([col2] BETWEEN @start AND @end)'

-- gratuitous use of parameter assignment here
-- could just as easily used @FromDate and @ToDate
-- in the @query and the parameter list
EXECUTE sp_executesql 
    @query
,   N'@start datetime, @end datetime'
,   @start = @FromDate
,   @end = @ToDate

Results

col1
3

T-SQL – Is Dynamic SQL in Stored Procedure Isolation Safe?

In your current setup, each of those queries is a separate transaction. Individually they are reading only committed data. But nothing is binding them together. If you want to guarantee the same underlying data for that view across both statements, you need to wrap them in a transaction, and add a Table Hint to the first query specifying to lock the table: WITH (TABLOCK, HOLDLOCK). I just tested this on a View and it does block access until the transaction completes, unless a query uses the WITH (NOLOCK) hint.

Regardless, you can do an easy test with your code to see how your current code works and how any changes might do what you are wanting:

Right before the EXEC sp_executesql @DynamicPivotQuery line, add the following two lines:

RAISERROR('Make a change now...', 10, 1) WITH NOWAIT;
WAITFOR DELAY '00:01:00.000' -- 1 minute pause

Then, run the code you have posted above. When you see "Make a change now" in the Messages tab, make a change to the underlying table. Once the WAITFOR finishes it will run the dynamic SQL and you should see the effects of your changes.

So the following should work:

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX);
DECLARE @ColumnName AS NVARCHAR(MAX);

BEGIN TRAN;
--Get distinct values of the PIVOT Column 
SELECT @ColumnName= ISNULL(@ColumnName + ',','') 
       + QUOTENAME(Course)
FROM (SELECT DISTINCT Course
      FROM CourseSalesView WITH (TABLOCK, HOLDLOCK)) AS Courses;

--Prepare the PIVOT query using the dynamic 
SET @DynamicPivotQuery = 
  N'SELECT Year, ' + @ColumnName + '
    FROM CourseSalesView
    PIVOT(SUM(Earning) 
          FOR Course IN (' + @ColumnName + ')) AS PVTTable';

--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery;
COMMIT TRAN;

Beyond that basic interaction, there are two other options:

Storing the results of the View into a local Temp Table (i.e. #TableName). This has the benefit of not locking the underlying table(s) of the View, but it will have to write the entire results of the view into tempdb and that does not sound like a scalable solution if the View returns 10k rows or more.
Enable SNAPSHOT ISOLATION (introduced in SQL Server 2005) which handles this for you without needing to explicitly lock the table:
- Snapshot Isolation in SQL Server
- ALTER DATABASE SET Options
- You will still need to wrap the two queries in an explicit Transaction, but no need for the Table Hint
- If you don't enable READ_COMMITTED_SNAPSHOT then you will need to issue SET TRANSACTION ISOLATION LEVEL SNAPSHOT before the BEGIN TRAN

Best Answer

Related Solutions

Sql-server – How to include a datetime parameter within a stored procedure along with string query

T-SQL – Is Dynamic SQL in Stored Procedure Isolation Safe?

Related Question