Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Pass Variable to Stored Procedure?

Status
Not open for further replies.

crystalreporting

Instructor
Feb 13, 2003
649
NZ
I have a stored procedure that uses the SQL2000 TEXTCOPY function to insert a BLOB image. The stored procedure is;

Code:
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;

Code:
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;

Code:
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.
 
You could make the filename and invoice number seperate paramaters and build the where clause inside the stored procedure (instead of the sp call).

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

Ryan
 
You may have to cast invoiceNumber as a varchar.

Code:
@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 [ponder] --me
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top