Stored procedure errors
-
I am making a stored procedure with 2 optional parameters customerid and companyname
eith a customerid or the companyname will be passed in so im running into a problem receiveibg several error messages.
i dont understand the errors can anyone help? The errors are saying declare scaliar value customerid but i did that and my whole select statement is underlined in red like the names dont exist within the table but they do
here is my procedure and shot of the errors
use [Cis11101_Northwind]GO
/****** Object: StoredProcedure [dbo].[spLMGetCustomer] Script Date: 5/4/2015 3:36:30 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Author:<Author,,Name>
-- Create date: <Create Date,,>
-- Description:<Description,,>
CREATE PROCEDURE [dbo].[spLMGetCustomer]
-- Add the parameters for the stored procedure here
@Companyname varchar (50)= null
@Customerid char (5) = null
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
if (@Customerid is null) And Not (@CompanyName is null)
Select orderid,customerid,employeeid,orderdate,requireddate,shippeddate,shipvia,freight
from Customers
where CompanyName=CompanyName
-
Ignoring the syntax problems (which is because you're missing a comma between the two parameters), this is generally not a good idea and will probably lead to performance problems in the long run.
http://sqlinthewild.co.za/index.php/2009/09/15/multiple-execution-paths/
Also, what is
where CompanyName=CompanyNamesupposed to do?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Ah, I was in the middle of typing up a response and Gail beat me to it!
+1 to what she said.
Also, your BEGIN is missing an END.
Cheers!
-
ok thanks I have changed the errors it the procedure . Im trying to create a procedure that if either one of the parameters are null then the other one will apply this is the error i get now after the changes
use [Cis11101_Northwind]GO
/****** Object: StoredProcedure [dbo].[spLMGetCustomer] Script Date: 5/4/2015 3:36:30 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Author:<Author,,Name>
-- Create date: <Create Date,,>
-- Description:<Description,,>
CREATE PROCEDURE [dbo].[spLMGetCustomer]
-- Add the parameters for the stored procedure here
@Companyname varchar (50)= null,
@Customerid char (5) = null
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
if (@Companyname is null) And Not (@customerid is null)
Select orderid,customerid,employeeid,orderdate,requireddate,shippeddate,shipvia,freight
from orders
where customerid=@customerid
if (@customerid is null) And Not (@companyname is null)
Select orderid,customerid,employeeid,orderdate,requireddate,shippeddate,shipvia,freight
from orders
where companyname=@companyname
END
-
That indicates that the column companyname does not exist in the Orders table, which I believe is true in the normal Northwind schema.
To pull in companyname, you'll need to join to the customers table on Orders.CustomerID=Customers.CustomerID and use Customers.CompanyName, if I remember the schema correctly (no guarantees on that one, but I think it's right :-)).
I hope this helps!
-
karodhill (5/4/2015)
Im trying to create a procedure that if either one of the parameters are null then the other one will applyPlease go and read the blog post I referenced. What you're doing is very prone to performance problems and is not a good way of doing things.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply