Executing stored procedure with parameters SQL Server 2005

Question

I have the following stored procedure below. When I try to execute the stored procedure with this code;

exec [transport].[dbo].[Delivery_Manifest_email] @RunNo = '3'

I get the following error

Msg 8146, Level 16, State 2, Procedure Delivery_Manifest_email, Line 0
Procedure Delivery_Manifest_email has no parameters and arguments were supplied.

I’m trying to pass the @RunNo parameter and I can't see why my syntax is wrong. The stored procedure executes fine when I manually set @RunNo parameter in the stored procedure itself. I don’t want to do it this way though. SQL Server is 2005.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[Delivery_Manifest]
AS 
    DECLARE @tableHTML  NVARCHAR(MAX);
    DECLARE @HTMLBody NVARCHAR(MAX)  
    DECLARE @XML NVARCHAR(MAX)
    DECLARE @s NVARCHAR(MAX)
    DECLARE @Driver NVARCHAR(MAX)
    DECLARE @Lorry NVARCHAR(MAX)
    DECLARE @recip NVARCHAR(MAX)
    DECLARE @varCheck BIT
    DECLARE @DueDate VARCHAR(10)
    DECLARE @RunNo NVARCHAR(MAX)

    SET @DueDate = '27/06/2016'
    SET @Driver = 'Test
    SET @Lorry ='Test'
    SET @s = 'Delivery Manifest for:  ' +CONVERT(VARCHAR(12),GETDATE(),107) +', Run number  '+@runno + ',  ' +@Driver +',  ' +@lorry

    SET @recip = '[email protected]' 

    IF EXISTS (SELECT [OrderNoOnManifest], [Lorry], [Driver], [Name], [Town], [POSTCODE], [Telephone], [DELIVERY NOTE], [Notes], [Shipping Weight (Kg)], [PackagingLength], [District], [ServiceLevel], [SpecificDelTime] FROM [Transport].[dbo].[DELIVERY_MANIFEST_MASTER] WHERE [Due Date] = @DueDate and [Run No] = @RunNo)

        SET @tableHTML = '<style type="text/css">
       table.tftable {font-size:12px;color:#333333;width:100%;border-width: 1px;border-color: #9dcc7a;border-collapse: collapse;}
table.tftable th {font-size:12px;color:#ffffff;background-color:#ff1a00;border-width: 1px;padding: 8px;border-style: solid;border-color: #9dcc7a;text-align:left;}
table.tftable tr {background-color:#ffffff;}
table.tftable td {font-size:12px;border-width: 1px;padding: 8px;border-style: solid;border-color: #9dcc7a;}
</style>
  <body>
  '
+ @s +'<p style="text-align:left;"><span style="font-family:Helvetica;font-size:12px;font-style:normal;font-weight:normal;text-decoration:none;text-transform:none;color:000033;"> </span>
</p>
<br>
<table class="tftable" border="2">'          
   + N'<th>Order</th>'
         + N'<th>Customer</th>'
          + N'<th>Town</th>'
+ N'<th>Postcode</th>'
  + N'<th>Tel. No.</th>'
   + N'<th>Order No</th>'
           + N'<th>Notes</th>'
  + N'<th>Weight (Kg)</th>'
     + N'<th>Dimension</th>'
          + N'<th>Instructions</th>'
  + N'<th>Service Level</th>'
  + N'<th>Delivery Time By</th></tr>'
  + CAST ( (  SELECT 
                   td = [OrderNoOnManifest], ''
                           , td = [Name] , ''
                             , td = [Town],  ''
                              , td = [POSTCODE] ,''
                               , td = [Telephone],''
                                  , td = [DELIVERY NOTE],''
                                    , td = [Notes],''
                                     , td = [Shipping Weight (Kg)],''
                                     , td = [PackagingLength],''
                                     , td = isnull([District],'None'),''
                                     , td = isnull([ServiceLevel],0),''
                                      ,td = isnull([SpecificDelTime],0),''
FROM            [Transport].[dbo].[DELIVERY_MANIFEST_MASTER]
WHERE [Due Date] = @DueDate and [Run No] = @RunNo
ORDER BY [OrderNoOnManifest] asc
                                                    FOR
                                                     XML PATH('tr')
                                                       , TYPE
                                                   ) AS NVARCHAR(MAX)) + N'</table>'

+'<br>'
+'<br>'
+  '<p style="text-align:left;"><span style="font-family:Helvetica;font-size:12px;font-style:normal;font-weight:bold;text-decoration:none;text-transform:none;color:000033;">TEST DELIVERY MANIFEST EMAIL</span>

'
BEGIN

    EXEC msdb.dbo.sp_send_dbmail 
   @blind_copy_recipients = @recip, 
    @profile_name = 'Test',  
    @subject = @s,
        @body = @tableHTML, 
        @body_format = 'HTML' ;

END

Siyual · Accepted Answer · 2016-06-30 15:12:09Z

3

You have no parameters defined for the stored procedure. You need to define them as such:

...
CREATE PROCEDURE [dbo].[Delivery_Manifest] (@RunNo NVARCHAR(MAX))
AS 
...

answered Jun 30, 2016 at 15:12

Siyual

17k8 gold badges47 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Executing stored procedure with parameters SQL Server 2005

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related