Stored Procedure | SQL with Manoj

Passing multiple/dynamic values to Stored Procedures & Functions | Part 5 – by passing JSON string

October 31, 2015 3 comments

This post is part of the [Passing multiple/dynamic values to Stored Procedures & Functions] series, and as well as the new feature Native JSON support in SQL Server 2016.

Adding the fifth part to this series we will use JSON string that will contain the set of values and pass as an JSON param variable to the SP. Then inside the SP we will parse this JSON and use those values in our SQL Queries, just like we did in previous posts with CSV/XML strings:

USE [AdventureWorks2014]
GO

-- Create an SP with NVARCHAR type parameter for JSON string:
CREATE PROCEDURE uspGetPersonDetailsJSON (
	@persons NVARCHAR(MAX)
)
AS
BEGIN
	--DECLARE @persons NVARCHAR(MAX)
	--SET @persons = '{"root":[{"Name":"Charles"},{"Name":"Jade"},{"Name":"Jim"},{"Name":"Luke"},{"Name":"Ken"}]}'
	
	SELECT Name
	INTO #tblPersons
	FROM OPENJSON (@persons, '$.root')
	WITH ( 
		Name NVARCHAR(100)
	)

	SELECT 
		BusinessEntityID, 
		Title, 
		FirstName, 
		MiddleName, 
		LastName, 
		ModifiedDate
	FROM [Person].[Person] PER
	WHERE EXISTS (
		SELECT * 
		FROM #tblPersons tmp 
		WHERE tmp.Name  = PER.FirstName
	)
	ORDER BY FirstName, LastName

	DROP TABLE #tblPersons
END
GO

-- Create JSON string:
DECLARE @json NVARCHAR(1000)
SET @json = N'{
  "root": [
    { "Name": "Charles" },
    { "Name": "Jade" },
    { "Name": "Jim" },
    { "Name": "Luke" },
    { "Name": "Ken" }
  ]
}'

-- Use the JSON string as parameter which calling the SP:
EXEC uspGetPersonDetailsJSON @json
GO

-- Check the output, objective achieved

-- Final Cleanup
DROP PROCEDURE uspGetPersonDetailsXML
GO

Thus you can also use JSON string similar to the way you used XML string, to pass multiple and dynamic number of parameters to your Stored Procedures.

As JSON feature is new to SQL Server 2016, so this method will only work with SQL Server 2016 and above versions.

Using GO statement within a Stored Procedure – MSDN TSQL forum

June 19, 2015 Leave a comment

–> Question:

I want to include GO statements to execute a large stored procedure in batches, how can I do that?

I have an SQL statement that is broken down into various sections, as it drops a temp table, then populates it, then generates another temp table based on the first one, then drops the first temp table and so on…….it enables me to execute all the code in one go.

I end each statement/block with a GO clause, to ensure it finishes running before the next peice of code.

However, when I try to incorprate this into a stored procedure, I cannot use the GO command. If I remove all the GO lines, the sp will run, but can I be confident that it’s running how it should, and that the lines of code near the end will not begin to execute before the lines above it have?

I’ve used a semicolon at the end of each statement in the sp?

–> My Answer:

First of all “GO” is not a SQL statement or command, this is just a keyword that your SSMS understands, and you can even change it to anything, check here.

Secondly “GO” is a batch separator that you use in SQL Queries, and Stored Procedure is itself a batch. If you put GO within a Stored Procedure, it will be an incomplete SP, and the rest part after GO will not be a part of the SP. More on MSDN about GO, link.

Now, why do you want to use GO within an SP?

If the SP is large, then try creating multiple SP and execute them in sequence.

–> Another Answer by Erland:

You cannot call script files from stored procedures. As I said two years ago, SQL Server is a server application – and SQLCMD is a client.

It’s a little confusing because you seem to use DML to mean DDL. (DML is Data Manipulation Language, that is INSERT, UPDATE, DELETE and also SELECT according to some, while DDL is Data Definition Language, that is CREATE/ALTER TABLE etc.)

Yes, if you want to interleave DDL and DML, you will need to make sure that the DML is not compiled until the DDL it depends on has been executed. Three solutions:

1) Separate batches. (Don’t forget to check @@trancount in the beginning of each batch.)=
2) But most commands in EXEC().
3) Have a program (in C# or whatever) to run the whole thing.

My preference is for the latter.

Ref Link and this.

Categories: SQL Server Questions Tags: batch separator, MSDN TSQL forum, Stored Procedure

Reference a table in a Stored Procedure (SP), and call the SP from the trigger? – MSDN TSQL forum

June 6, 2013 Leave a comment

–> Question:

I have three tables “a”, “b”, “c “.

I want to make one trigger on table “a” and one on table “b”, the trigger inturn calls a stored proc(SP) to perform insertion deletion task on table “c”.

When some body inserts or updates table “a” , the trigger on table “a” inserts or updates table “c” by calling the stored proc(SP), and there after updates table “b” from trigger it self (this stmt is below the call of (SP).

Q1) i am using table “a” in (SP), i wanted to know that, is it correct to use the table “a” in stored proc(SP) to get the new inserted record or updated record to insert or update table “c” as the control is till in tigger of table “a”?

Well when table “b” gets updated from the trigger of table “a” , then the trigger of table “b” gets activated and it inserts or update the table “c” by calling (SP).

Q2) at this place when trigger of table”b” calls the same (SP) to insert table “c”, it uses table “a” and “b” to insert update tabel “c”, at this point also the control is still in trigger, so i whould like to know is it correct (technically).

I know that by changing few things in tables i can make things easy

I read this in msdn:

“AFTER specifies that the trigger is fired only when all operations specified in the triggering SQL statement have executed successfully. All referential cascade actions and constraint checks also must succeed before this trigger fires.”

But I would to know that, what exactally happens in above case , how and when event are fired and gets affected. and finally is it correct?

–> My Answer:

Why are you creating a separate Stored Proc to fire from the trigger?

You can have the SP logic in the Trigger itself. If you use an SP, then you would not get the INSERTED & DELETED Trigger/Magic tables that you can get in the Trigger.

Also you can avoid Triggers and create single SP to add records in Table a & b and update the table c in a single transaction.

Ref Link.

Categories: SQL Server Questions Tags: MSDN TSQL forum, Stored Procedure, TRIGGER, Trigger vs SP

Passing multiple/dynamic values to Stored Procedures & Functions | Part 4 – by using TVP

September 10, 2012 2 comments

This is the last fourth part of this series, in previous posts we talked about passing multiple values by following approaches: CSV, XML, #table. Here we will use a new feature introduced in SQL Server 2008, i.e. TVP (Table Valued Parameters).

As per MS BOL, TVPs are declared by using user-defined table types. We can use TVPs to send multiple rows of data to Stored Procedure or Functions, without creating a temporary table or many parameters. TVPs are passed by reference to the routines thus avoiding copy of the input data.

Let’s check how we can make use of this new feature (TVP):

-- First create a User-Defined Table type with a column that will store multiple values as multiple records:
CREATE TYPE dbo.tvpNamesList AS TABLE 
(
	Name NVARCHAR(100) NOT NULL,
    PRIMARY KEY (Name)
)
GO

-- Create the SP and use the User-Defined Table type created above and declare it as a parameter:
CREATE PROCEDURE uspGetPersonDetailsTVP (
	@tvpNames tvpNamesList READONLY
)
AS
BEGIN
	
	SELECT BusinessEntityID, Title, FirstName, MiddleName, LastName, ModifiedDate
	FROM [Person].[Person] PER
	WHERE EXISTS (SELECT Name FROM @tvpNames tmp WHERE tmp.Name  = PER.FirstName)
	ORDER BY FirstName, LastName

END
GO

-- Now, create a Table Variable of type created above:
DECLARE @tblPersons AS tvpNamesList

INSERT INTO @tblPersons
SELECT Names FROM (VALUES ('Charles'), ('Jade'), ('Jim'), ('Luke'), ('Ken') ) AS T(Names)

-- Pass this table variable as parameter to the SP:
EXEC uspGetPersonDetailsTVP @tblPersons
GO
-- Check the output, objective achieved 🙂


-- Final Cleanup
DROP PROCEDURE uspGetPersonDetailsTVP
GO

So, we saw how we can use TVPs with Stored Procedures, similar to this they are used with UDFs.

TVPs are a great way to pass array of values as a single parameter to SPs and UDFs. There is lot of know and understand about TVP, their benefits and usage, check this [link].

Categories: SQL Server 2008, SQL Tips, Stored Procedures Tags: multiple values to Stored Procedures, Stored Procedure, Table Valued Parameters, TVP

Passing multiple/dynamic values to Stored Procedures & Functions | Part 3 – by using #table

September 9, 2012 3 comments

In my previous posts we saw how to pass multiple values to an SP by using CSV list and XML data, which are almost of same type. Here in this post we will see how we can achieve the same objective without passing values as parameters and by using temporary (temp, #) tables.

Here in the third part of this series the Stored Procedure will be created in such a way that it will use a Temporary Table, which does not exist in compile time. But at run time the temp-table should be created before running the SP. In this approach there is no need to pass any parameter with the SP, let’s see how:

-- Create Stored Procedure with no parameter, it will use the temp table created outside the SP:
CREATE PROCEDURE uspGetPersonDetailsTmpTbl
AS
BEGIN
	
	SELECT BusinessEntityID, Title, FirstName, MiddleName, LastName, ModifiedDate
	FROM [Person].[Person] PER
	WHERE EXISTS (SELECT Name FROM #tblPersons tmp WHERE tmp.Name  = PER.FirstName)
	ORDER BY FirstName, LastName

END
GO

-- Now, create a temp table, insert records with same set of values we used in previous 2 posts:
CREATE TABLE #tblPersons (Name NVARCHAR(100))

INSERT INTO #tblPersons
SELECT Names FROM (VALUES ('Charles'), ('Jade'), ('Jim'), ('Luke'), ('Ken') ) AS T(Names)

-- Now execute the SP, it will use the above records as input and give you required results:
EXEC uspGetPersonDetailsTmpTbl
-- Check the output, objective achieved 🙂

DROP TABLE #tblPersons
GO

-- Final Cleanup
DROP PROCEDURE uspGetPersonDetailsTmpTbl
GO

This approach is much better than the previous 2 approaches of using CSV or XML data.

The values will be entered in temp tables and then will be accessed inside the SP. There is no parsing involved as the records are directly read from temp table and used in SQL query just like normal queries. Also there is no limit of records if you compare with CSVs & XML data.

But, the catch here is, the temp table should exist before executing the SP, if for some reason it is not there the code will crash. But its rare and can be handled by some checks.

In my next and last [blog post] of this series we will see a new feature of SQL Server 2008 i.e. TVP, which can be used in such type of scenarios, check here.

Categories: SQL Tips, Stored Procedures Tags: multiple values to Stored Procedures, Stored Procedure

Older Entries