Must declare the scalar variable

The sp that receives the Table name as a parameter.
I keep receiving the error message

Must declare the scalar variable "@ProdID".

I tired many different ways and didn't work.

Please help.

--------------------------------------------------
USE [MyDB]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[SP_Test]
      -- Add the parameters for the stored procedure here
      @schema sysname,
      @table_name sysname,
      @ProdID varchar(10),
      @EntryDateTime  datetime,
      @SeqNo bigint,
      @File_path varchar (250)
AS
BEGIN
SET @table_name = RTRIM(@table_name)
DECLARE @cmd AS NVARCHAR(max)

SET NOCOUNT ON
SET @cmd = N'INSERT INTO ' +  QUOTENAME(@schema) + N'.' + QUOTENAME(@table_name) + N' (ProdID, EntryDateTime, InsDateTime, SeqNo,File_path) VALUES (@ProdID, @EntryDateTime, SYSDATETIME(),  @SeqNo, @File_path)'
print @cmd

EXEC sp_executesql @cmd
END

GO
-------------------------------------------------------------------
pcssecureAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
pivarConnect With a Mentor Commented:
Looking at the datatypes, the line should read:

SET @cmd = N'INSERT INTO ' +  QUOTENAME(@schema) + N'.' + QUOTENAME(@table_name) + N' (ProdID, EntryDateTime, InsDateTime, SeqNo,File_path) VALUES (''' +@ProdID + ''', ''' +CONVERT(varchar, @EntryDateTime, 101) + ''', ''' +CONVERT(varchar, SYSDATETIME(), 101) + ''', ' +CONVERT(varchar, @SeqNo) + ', ''' +@File_path + ''')'
0
 
pivarCommented:
Hi,

You're trying to use the variable in the dynamic sql, that is not possible. Either use the value directly

SET @cmd = N'INSERT INTO ' +  QUOTENAME(@schema) + N'.' + QUOTENAME(@table_name) + N' (ProdID, EntryDateTime, InsDateTime, SeqNo,File_path) VALUES (''' +@ProdID + ''', ''' +@EntryDateTime + ''', ''' +SYSDATETIME() + ''', ''' +@@SeqNo + ''', ''' +@@File_path + ''')'

or use

sp_executesql

/peter
0
 
pcssecureAuthor Commented:
Thanks for the help but still not working from my VB app. But it is working from SQL Server Management Studio. So I guess we are very close to the solution.

            Dim EntryTime As SqlParameter = New SqlParameter("@EntryTime ", SqlDbType.DateTime)
            EntryTime .Value =In_ EntryTime 'Passed in as parameter type DateTime
            myCommand.Parameters.Add(EntryTime )

Error : Conversion failed when converting date and/or time from character string.

Not sure why should I use convert when I pass in value as Type DateTime?
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
pivarCommented:
Dim EntryTime As SqlParameter = New SqlParameter("@EntryTime ", SqlDbType.DateTime) should be
Dim EntryTime As SqlParameter = New SqlParameter("@EntryDateTime ", SqlDbType.DateTime)?

I think the error comes from sql, but I can't see anything obvious, so can you log what the content of @cmd is?
0
 
pcssecureAuthor Commented:
Thanks. Problem solved but strange. I need to capture up to milliseconds. So when I use

SET @cmd = N'INSERT INTO ' +  QUOTENAME(@schema) + N'.' + QUOTENAME(@table_name) + N' (ProdID, EntryDateTime, InsDateTime, SeqNo,File_path) VALUES (''' +@ProdID + ''', ''' +CONVERT(varchar, @EntryDateTime, 121) + ''','''+ CONVERT(varchar, SYSDATETIME(),121) + ''', ' +CONVERT(varchar, @SeqNo) + ', ''' +@File_path + ''')'
Produced Error : Conversion failed when converting date and/or time from character string.

So I changed to

SET @cmd = N'INSERT INTO ' +  QUOTENAME(@schema) + N'.' + QUOTENAME(@table_name) + N' (ProdID, EntryDateTime, InsDateTime, SeqNo,File_path) VALUES (''' +@ProdID + ''', ''' +CONVERT(varchar, @EntryDateTime, 121) + ''', SYSDATETIME() , ' +CONVERT(varchar, @SeqNo) + ', ''' +@File_path + ''')'
And it works fine.
Any idea why caused an error in the first one when using 121 with SYSDATETIME()?
Is using 121 correct in convert function to get up to millisecond?
I got the working one by trial and error.
Thanks.
0
 
pcssecureAuthor Commented:
Thanks and hope to here from you.
0
 
pivarCommented:
I'm guessing now, but it could be that the problem lies in that SYSDATETIME returns a DATETIME2.
You should see ms with 121 (yyyy-mm-dd hh:mi:ss.mmm(24h))
Have you tried another format, perhaps 109 or 113? Or try to convert to DATETIME first?
I'm can not test it myself right now.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.