TSQL Stored Procedure with parameters & variables

Question

I am a beginner with TSQL scripting and I need a hand to get my stored procedure to work as intended.

I want to use the @date parameter in my sp to take logg@date - in ccyymmdd format. Also, is there any security flaws or optimisation i could think of more than the Tablock? Could tablock become a problem in the future?

To clearify problem: When I do the Bulk Insert, I want the data to get into the db depending on @datum and currently it is not working as intended. Any hints would be appreciated. batch file will be running on daily schedule to insert data into dbname@datum.

Current SP code:

USE [dbname]
GO
/****** Object:  StoredProcedure [dbo].[fillDbTablelogg]    Script Date: 2016-02-26 16:47:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

 =============================================
 Author:        <Author,,Name>
 Create date:   <Create Date,,>
 Description:   <Description,,>
 =============================================
ALTER PROC [dbo].[fillDbname]
@datum date(8)
AS
BEGIN

SET NOCOUNT ON;
Declare @bulkInsert NVARCHAR(MAX)



Declare @pathRail nvarchar(100)  = 'x:\xxxx\SYSTEMID\';
Declare @pathDate nvarchar(100) = convert(char, getdate(), 12); 
--Want to use my @Date parameter instead, sending in 8 digits so ccyymmdd
Declare @fromPath NVARCHAR(MAX) = '@pathRail' + '20' + '@pathDate' + '.tab';
--should print 'x:\xxxx\SYSTEMID\pathRailccyymmdd.tab'


Declare @ErrorPath nvarchar(100) = 'x:\xxxx\SYSTEMID\err';
--x:\xxxx\SYSTEMID\err2014-11-22.12:45:34.log
Declare @ErrorNow nvarchar(100) = curdate() + '.' + curtime() + '.log';
Declare @errorFilePath NVARCHAR (100) = @ErrorPath + @ErrorNow;

--Will change this formatfilename later to something more suiting, I have a working XML format file created with bcp.
Declare @formatFile nvarchar (100) = 'x:\xxxx\SYSTEMID\dbo.nameccyymmdd.xml';

set @bulkInsert =  '
BULK INSERT dbname.logg' + @datum + 'FROM' + @fromPath +'
WITH 
    (
   FIRSTROW = 1,
   ORDER (Id),
   CODEPAGE = Finnish_Swedish_CI_AS,
   FIELDTERMINATOR = \t,
   ROWTERMINATOR = 0x0a,
   TABLOCK,
   ERRORFILE = @errorFilePath,
   FORMATFILE = @formatFile, 
   --KEEPNULLS
   );'
   exec(@bulkInsert)
   end

Fixed: Added datatypes to all variables. Made sure all variables that was used were created first. edited @datum variable to datatype date instead of varchar.

First of all you should use the correct datatypes...always. If you are dealing with date information you should use date, not varchar. Then you have tons of syntax errors, variables defined with no datatypes, variable being used that aren't defined. — Sean Lange
– Sean Lange, Commented Feb 29, 2016 at 19:24
What variable is not defined in script? Thanks for input @SeanLange — Froxer
– Froxer, Commented Feb 29, 2016 at 19:39
@datum is not defined anywhere in there. Also, what is curdate() and curtime(). Those look like mysql functions. And you really should make you input parameter be a date instead of varchar(8). What happens when you pass in 'a8djf'? — Sean Lange
– Sean Lange, Commented Feb 29, 2016 at 19:43
'@datum' is supposed to replace '@pathDate' - It will be implemented with a batch file (.bat) on a daily schedule. — Froxer
– Froxer, Commented Feb 29, 2016 at 19:45
So what is wrong with your script at this point? Are you getting an error? — Tab Alleman
– Tab Alleman, Commented Feb 29, 2016 at 19:48

Tab Alleman · Accepted Answer · 2016-02-29 19:22:40Z

1

Your DECLARE statements need to include datatype:

Declare @pathRail varchar(31) = 'x:\xxxx\SYSTEMID\';
Declare @pathDate varchar(15) = convert(char, getdate(), 12);

answered Feb 29, 2016 at 19:22

Tab Alleman

31.8k7 gold badges41 silver badges54 bronze badges

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

1 Comment

Froxer Over a year ago

Fixed syntax errors & edited some logic. I got a working formating file now! Accepted as answer

Collectives™ on Stack Overflow

TSQL Stored Procedure with parameters & variables

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related