Pass Variable to Stored Procedure?

[crystalreporting]

I have a stored procedure that uses the SQL2000 TEXTCOPY function to insert a BLOB image. The stored procedure is;

CREATE PROCEDURE sp_textcopy ( 
  @srvname     varchar (30),
  @login       varchar (30),
  @password    varchar (30),
  @dbname      varchar (30),
  @tbname      varchar (30),
  @colname     varchar (30),
  @filename    varchar (30),
  @whereclause varchar (40),
  @direction   char(1))
AS
DECLARE @exec_str varchar (255)
SELECT @exec_str = 
         'C:\textcopy /S ' + @srvname +
         ' /U ' + @login + 
         ' /P ' + @password + 
         ' /D ' + @dbname +
         ' /T ' + @tbname + 
         ' /C ' + @colname + 
         ' /W "' + @whereclause +
         '" /F ' + @filename + 
         ' /' + @direction
EXEC master..xp_cmdshell @exec_str

When I call this from a simple query it works fine;

exec sp_textcopy @srvname = '(local)', 
        @login = 'sa', 
        @password = 'notrealpassword', 
        @dbname = '[002]', 
        @tbname = 'ImageTable',
        @colname = 'ImageField', 
        @filename = 'c:\Export\50.pdf', 
        @whereclause = " where OrderNumber = '      50'",
        @direction = 'I'

However I want to use this as part of a cursor/fetch next statement so I need to make the @filename and @whereclause dynamic using a variable called @InvoiceNumber. I tried;

exec sp_textcopy @srvname = '(local)', 
        @login = 'sa', 
        @password = 'notrealpassword', 
        @dbname = '[002]', 
        @tbname = 'ImageTable',
        @colname = 'ImageField', 
        @filename = 'c:\Export\' + LTRIM(@InvoiceNumber) + '.pdf', 
        @whereclause = " where OurRef = '" + @InvoiceNumber + "'",
        @direction = 'I'

But I get Incorrect Syntax errors. I've tried many combinations of single quotes, double quotes and ampersands instead of plusses but I'm completely lost...


Peter Shirley
Macola Consultant, PA and surrounding states.

http://www.erpessentials.com

 

[RyanEK]

You could make the filename and invoice number seperate paramaters and build the where clause inside the stored procedure (instead of the sp call).

' /W " where ' + @filename + ' = "' + @invoicenumber + '"'

Ryan

 

[Gooser]

You may have to cast invoiceNumber as a varchar.

@filename = 'c:\Export\' + [b]CAST([/b]LTRIM(@InvoiceNumber)[b]AS varchar(32))[/b] + '.pdf',

v/r

Gooser

Why do today
that which may not need to be done tomorrow --me