{"id":8833,"date":"2023-11-16T07:41:00","date_gmt":"2023-11-16T06:41:00","guid":{"rendered":"https:\/\/expert-only.com\/?p=8833"},"modified":"2023-11-23T12:07:53","modified_gmt":"2023-11-23T11:07:53","slug":"create-sql-server-stored-procedure-with-parameters","status":"publish","type":"post","link":"https:\/\/expert-only.com\/en\/t-sql\/create-sql-server-stored-procedure-with-parameters\/","title":{"rendered":"How to create a SQL Server stored procedure with parameters ?"},"content":{"rendered":"\n

T-SQL tutorial to create a SQL Server stored procedure with input parameters and T-SQL variables.<\/em><\/h4>\n\n\n\n

Create a SQL Server stored procedure with parameters and default values. The goal of SQL procedures is to group and schedule a set of SQL or T-SQL commands. But how to integrate a dynamic SELECT query on the Customers table of a SQL Server database into a stored procedure?<\/p>\n\n\n\n

For example, instead of displaying all the data from the table, display a single customer. We need to filter the table using the parameter value passed as an input hard coded value or variable. <\/p>\n\n\n\n

Create the sample table<\/h3>\n\n\n\n

Before running the script to create the stored procedure, create the clients table first. Indeed, use this SQL Server script to create the Clients table with a simple structure and 3 columns: <\/p>\n\n\n\n

    \n
  1. ClientNumber<\/li>\n\n\n\n
  2. Name<\/li>\n\n\n\n
  3. City<\/li>\n<\/ol>\n\n\n\n
    -- Create the clients table to store the number, the name and the city\nCREATE TABLE [dbo].[CLIENTS] (\n   [ClientNumber]  int IDENTITY(1,1),\n   [Name]          nvarchar(20) UNIQUE,\n   [City]          nvarchar(20)\n)\nGO<\/pre>\n\n\n
    \n
    \"SQL
    SQL Server query to display the contents of the CLIENTS table<\/em><\/figcaption><\/figure><\/div>\n\n\n
    1. T-SQL code to create a procedure with one parameter<\/h2>\n\n\n\n
    This is an example of a SQL stored procedure that uses a single parameter. This parameter is the customer number. The procedure displays the customer whose number is passed as a parameter.<\/p>\n\n\n\n
    CREATE PROCEDURE uspGetClient\n@NoClient INT\nAS\n\tSELECT \t*\n\tFROM \t[dbo].[CLIENTS]\n\tWHERE \t[NOCLIENT] = @NoClient\nGO\n<\/pre>\n\n\n\n
    2. Call the stored procedure using EXEC or EXECUTE<\/h2>\n\n\n\n
    To call the stored procedure, execute the code as follows with EXEC or EXECUTE :<\/p>\n\n\n\n
    EXEC uspGetClient @NoClient = 3;\n\nEXECUTE uspGetClient @NoClient = 3;\n<\/pre>\n\n\n
    \n
    \"Manage
    Manage SQL Server stored procedure parameters with variables<\/strong><\/figcaption><\/figure><\/div>\n\n\n
    In the previous example, the procedure call passes the value of the parameter. Similarly, execute the SQL Server procedure without passing the @NoClient parameter value:<\/p>\n\n\n\n
    EXEC uspGetClient;\n<\/pre>\n\n\n\n
    Then the following SQL error message is displayed because the client number parameter is mandatory and is not assigned a default value:<\/p>\n\n\n\n
    Procedure or function ‘uspGetClient’ expects parameter ‘@NoClient’, which was not supplied.<\/em><\/p>\n\n\n\n
    3. Manage parameters default values in stored procedure<\/h2>\n\n\n\n
    To go further and manage the default values, it is simple, in most cases, it is advisable to pass a value to the parameters. In practice, this is not always possible or even useful. To foresee the case where a call to the procedure is made without parameters, use the NULL value. Indeed NULL allows to initialize a parameter value in the procedure and to avoid errors.<\/p>\n\n\n\n
    After that, execute this second version of the stored procedure without parameters. This time, no errors and the query return no rows because all the clients in the table have a client number.<\/p>\n\n\n\n
    -- Test if the procedure exists and delete it\nIF EXISTS (\n   SELECT name \n   FROM sysobjects \n   WHERE name = 'uspGetClient' AND type = 'P'\n)\n  DROP PROCEDURE uspGetClient\nGO\n\n-- Create the stored procedure with a default value\nCREATE PROCEDURE uspGetClient \n  @NoClient int = NULL\nAS\n  SELECT   *\n  FROM   [dbo].[CLIENTS]\n  WHERE   [NOCLIENT] = @NoClient\nGO\n\n-- Execute the stored procedure without passing any parameters\nEXEC uspGetClient;\n<\/pre>\n\n\n
    \n
    \"Delete,
    Delete, create, and execute a SQL Server stored procedure without using parameters<\/figcaption><\/figure><\/div>\n\n\n
    4. SQL Server stored procedure with multiple parameters<\/h2>\n\n\n\n
    This is the same principle as with a single parameter, and each parameter must be listed, initialized, and called independently. Thus, enumerate and separate each parameter and its data type with a comma, as in this example of SQL code:<\/p>\n\n\n\n
    -- Test if the procedure exists and delete it\nIF EXISTS (\n   SELECT name \n   FROM \tsysobjects \n   WHERE \tname = 'uspGetClient' AND type = 'P'\n)\n  DROP PROCEDURE uspGetClient\nGO\n\n-- Create the same procedure with two parameters and default values\nCREATE PROCEDURE uspGetClient \n  @ClientNumber   \tint = NULL, \n  @City   \t\tnvarchar(20) = NULL\nAS\n  SELECT   *\n  FROM   \t[dbo].[CLIENTS]\n  WHERE  \t[ClientNumber]  = @ClientNumber\n    OR   \t[City]      = @City\nGO\n\n-- Call the procedure with different parameter combinations\nEXEC uspGetClient @ClientNumber = 1, @City = 'Lyon';\n\nEXEC uspGetClient @ClientNumber = 1;\n\nEXEC uspGetClient @City = 'Paris';\n<\/pre>\n\n\n\n
    Indeed, one can call each of the parameters alone, or both simultaneously. It is possible to pass no parameters because the procedure initializes the default values with the value NULL.<\/p>\n\n\n
    \n
    \"Delete,
    Delete, create, and execute a SQL Server stored procedure with parameters.<\/figcaption><\/figure><\/div>\n\n\n
    SQL procedures with parameters allow flexible code<\/h3>\n\n\n\n
    Finally, calling the uspGetClient<\/em> stored procedure without parameters is possible. In this case, it returns all the data contained in the Clients table because the logical OR operator is used in the WHERE clause. This scripts simply displays data from a customers table, however stored procedures can also be used to manipulate objects, like creating and rebuilding indexes<\/a>.<\/p>\n\n\n\n
    To continue learning, this other tutorial explains step by step how to alter a SQL Server stored procedure<\/a><\/strong>.<\/p>\n\n\n\n
    \n
    How To Alter A SQL Server Stored Procedure ?<\/a><\/blockquote>