Help with delimited SQL

• nadersam

  SSCertifiable

  Points: 6542

  More actions

  December 22, 2016 at 2:47 am

  #332916

  Hi Guys,

  I am trying to execute an array of comma delimited SQLs in a stored procedure.

  I call it as below

  declare @Parameter as nvarchar(max)

  set @Parameter ='insert into Orderlog (trans_no) values (1)||||insert into Orderlog (trans_no) values (2)||||insert into Orderlog (trans_no) values (3)||||insert into Orderlog (trans_no) values (4)'

  --select part FROM SplitStrs(@Parameter,'||||')

  exec addorders @Parameter

  The objects i use are attached in this topic, please help with that as it's giving me error while if i take the code inside the stored procedure and run it outside it works fine.

  Thanks in advance

  Nader

• Thom A

  SSC Guru

  Points: 99133

  More actions

  December 22, 2016 at 4:32 am

  #1919294

  You'll find it unlikely that anyone will be happy to open that RAR file of yours, as we don't know what's contained inside it (especially as many of us browse the forums while at work).

  When you say objects, what do you mean. I assume you haven't uploaded a table in your archive, so what is it? if it's an SP, just post the SQL here. Remember to use your IFCODE brackets, [code="sql"] [/code]. If would help if we knew what your Stored Procedure, AddOrders did.

  Thanks.

  P.S.

  If your looking to create multiple SQL statement and excute them, you would need to do something like this:

  DECLARE @sql VARCHAR(MAX);

  SET @sql='SELECT 1 AS FirstValue;

  SELECT 2 AS SecondValue;

  SELECT 3 AS ThirdValue, 4 AS FouthValue;'

  EXEC (@SQL);

  This will execute all 3 statements separately.

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• nadersam

  SSCertifiable

  Points: 6542

  More actions

  December 22, 2016 at 4:46 am

  #1919295

  Thank you very much for your reply.

  I was able to solve issue, it happened because of missed up single quotes locations.

  Thanks for the tip you sent , i didn't know that and was going to split the SQLs and run each one separately.

  I reattached my script as text file.

  My question is if one of the SQLs contained a semicolon i guess it will mess up the execution right?

  Thanks Nader

• Thom A

  SSC Guru

  Points: 99133

  More actions

  December 22, 2016 at 4:57 am

  #1919298

  nadersam (12/22/2016)

  ---

  My question is if one of the SQLs contained a semicolon i guess it will mess up the execution right?

  Thanks Nader

  Semicolons won't be a problem at all (notice that I have 3 in the above example), and if you didn't use semi colons, you couldn't use a WITH clause. I'd actually suggest you make sure you do include them when using D-SQL. as it makes your statements easier to separate if you need to debug (plus it's deprecated to not use them).

  One thing you can't include, however, is GO as it isn't actually a SQL work, but a batch separator. For example, this wouldn't work:

  USE DevTestDB;

  GO

  IF EXISTS (SELECT name FROM sys.tables WHERE name = 'SampleGO') BEGIN

  DROP TABLE SampleGO;

  END

  DECLARE @sql VARCHAR(MAX);

  SET @sql = 'CREATE TABLE SampleGO (ID INT);

  GO --THE D-SQL WILL FAIL HERE

  INSERT INTO SampleGO

  VALUES (1),

  (2),

  (3);'

  EXEC (@SQL);

  GO

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• Phil Parkin

  SSC Guru

  Points: 246990

  More actions

  December 22, 2016 at 5:48 am

  #1919308

  nadersam (12/22/2016)

  ---

  Hi Guys,

  I am trying to execute an array of comma delimited SQLs in a stored procedure.

  I call it as below

  declare @Parameter as nvarchar(max)

  set @Parameter ='insert into Orderlog (trans_no) values (1)||||insert into Orderlog (trans_no) values (2)||||insert into Orderlog (trans_no) values (3)||||insert into Orderlog (trans_no) values (4)'

  --select part FROM SplitStrs(@Parameter,'||||')

  exec addorders @Parameter

  The objects i use are attached in this topic, please help with that as it's giving me error while if i take the code inside the stored procedure and run it outside it works fine.

  Thanks in advance

  Nader

  If you can modify things so that your delimiter is only a single character, you can do away with the need for a new function, as SQL Server 2016 now has STRING_SPLIT().

  The absence of evidence is not evidence of absence.
  Martin Rees

  You can lead a horse to water, but a pencil must be lead.
  Stan Laurel

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  December 22, 2016 at 5:51 am

  #1919309

  nadersam (12/22/2016)

  ---

  Hi Guys,

  I am trying to execute an array of comma delimited SQLs in a stored procedure.

  I call it as below

  declare @Parameter as nvarchar(max)

  set @Parameter ='insert into Orderlog (trans_no) values (1)||||insert into Orderlog (trans_no) values (2)||||insert into Orderlog (trans_no) values (3)||||insert into Orderlog (trans_no) values (4)'

  --select part FROM SplitStrs(@Parameter,'||||')

  exec addorders @Parameter

  The objects i use are attached in this topic, please help with that as it's giving me error while if i take the code inside the stored procedure and run it outside it works fine.

  Thanks in advance

  Nader

  It would be a heck of a lot easier, neater, more transparent, more familiar to future coders on the project and performant if you passed in a comma-delimited list of trans-no values and inserted them into the Orderlog table after splitting into a temp table.

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• Thomas Rushton

  SSC-Insane

  Points: 22649

  More actions

  December 22, 2016 at 9:03 am

  #1919368

  Phil Parkin (12/22/2016)

  ---

  nadersam (12/22/2016)

  ---

  Hi Guys,

  I am trying to execute an array of comma delimited SQLs in a stored procedure.

  I call it as below

  declare @Parameter as nvarchar(max)

  set @Parameter ='insert into Orderlog (trans_no) values (1)||||insert into Orderlog (trans_no) values (2)||||insert into Orderlog (trans_no) values (3)||||insert into Orderlog (trans_no) values (4)'

  --select part FROM SplitStrs(@Parameter,'||||')

  exec addorders @Parameter

  The objects i use are attached in this topic, please help with that as it's giving me error while if i take the code inside the stored procedure and run it outside it works fine.

  Thanks in advance

  Nader

  If you can modify things so that your delimiter is only a single character, you can do away with the need for a new function, as SQL Server 2016 now has STRING_SPLIT().

  ...assuming, of course, the order of the returned items isn't important - STRING_SPLIT isn't guaranteed to return data in the order in which it was received. There's a Connect item about this.

  Thomas Rushton
  blog: https://thelonedba.wordpress.com

• nadersam

  SSCertifiable

  Points: 6542

  More actions

  December 22, 2016 at 10:01 am

  #1919385

  Thank you guys for all the replies , i really appreciate it

• Jeff Moden

  SSC Guru

  Points: 1004425

  More actions

  December 22, 2016 at 4:26 pm

  #1919452

  Phil Parkin (12/22/2016)

  ---

  nadersam (12/22/2016)

  ---

  Hi Guys,

  I am trying to execute an array of comma delimited SQLs in a stored procedure.

  I call it as below

  declare @Parameter as nvarchar(max)

  set @Parameter ='insert into Orderlog (trans_no) values (1)||||insert into Orderlog (trans_no) values (2)||||insert into Orderlog (trans_no) values (3)||||insert into Orderlog (trans_no) values (4)'

  --select part FROM SplitStrs(@Parameter,'||||')

  exec addorders @Parameter

  The objects i use are attached in this topic, please help with that as it's giving me error while if i take the code inside the stored procedure and run it outside it works fine.

  Thanks in advance

  Nader

  If you can modify things so that your delimiter is only a single character, you can do away with the need for a new function, as SQL Server 2016 now has STRING_SPLIT().

  Just don't rely on it for ordinal position of the split out elements. 😉

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Chris Wooding

  SSCarpal Tunnel

  Points: 4561

  More actions

  December 23, 2016 at 4:52 am

  #1919499

  This method looks highly vulnerable to SQL injection attacks.

• nadersam

  SSCertifiable

  Points: 6542

  More actions

  December 23, 2016 at 5:15 am

  #1919502

  Chris Wooding (12/23/2016)

  ---

  This method looks highly vulnerable to SQL injection attacks.

  Which one Chris?

• Chris Wooding

  SSCarpal Tunnel

  Points: 4561

  More actions

  December 23, 2016 at 7:20 am

  #1919521

  Running multiple SQL statements passed in as a string.

• Phil Parkin

  SSC Guru

  Points: 246990

  More actions

  December 23, 2016 at 7:41 am

  #1919522

  Chris Wooding (12/23/2016)

  ---

  Running multiple SQL statements passed in as a string.

  If the statements to be executed come directly from a table whose contents cannot be manipulated, except by suitably authorised people, I don't see a massive issue.

  The absence of evidence is not evidence of absence.
  Martin Rees

  You can lead a horse to water, but a pencil must be lead.
  Stan Laurel