ساختن stored procedure در MSSQL با استفاده از پارامترها

یک پارامتر

CREATE PROCEDURE uspGetAddress @City nvarchar(30) AS SELECT *  FROM AdventureWorks.Person.Address WHERE City = @City GO To call this stored procedure we would execute it as follows:  EXEC uspGetAddress @City = 'New York' We can also do the same thing, but allow the users to give us a starting point to search the data.  Here we can change the "=" to a LIKE and use the "%" wildcard.  CREATE PROCEDURE uspGetAddress @City nvarchar(30)  AS  SELECT *  FROM AdventureWorks.Person.Address  WHERE City LIKE @City + '%'  GO In both of the proceeding examples it assumes that a parameter value will always be passed. If you try to execute the procedure without passing a parameter value you will get an error message such as the following:  Msg 201, Level 16, State 4, Procedure uspGetAddress, Line 0  Procedure or function 'uspGetAddress' expects parameter '@City', which was not supplied. 
CREATE PROCEDURE uspGetAddress @City nvarchar(30) = NULL AS SELECT * FROM AdventureWorks.Person.Address WHERE City = @City GO We could change this stored procedure and use the ISNULL function to get around this.  So if a value is passed it will use the value to narrow the result set and if a value is not passed it will return all records. (Note: if the City column has NULL values this will not include these values. You will have to add additional logic for City IS NULL)  CREATE PROCEDURE uspGetAddress @City nvarchar(30) = NULL AS SELECT * FROM AdventureWorks.Person.Address WHERE City = ISNULL(@City,City) GO 

چند پارامتر

CREATE PROCEDURE uspGetAddress @City nvarchar(30) = NULL, @AddressLine1 nvarchar(60) = NULL AS SELECT * FROM AdventureWorks.Person.Address WHERE City = ISNULL(@City,City) AND AddressLine1 LIKE '%' + ISNULL(@AddressLine1 ,AddressLine1) + '%' GO To execute this you could do any of the following:  EXEC uspGetAddress @City = 'Calgary' --or EXEC uspGetAddress @City = 'Calgary', @AddressLine1 = 'A' --or EXEC uspGetAddress @AddressLine1 = 'Acardia' -- etc... 

دیدگاهتان را بنویسید

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *

1 × دو =