October 7, 2014 at 12:10 pm
SP called from BAT file to pass variable @myparam1. I can use a Print cmd in SP to see variable is passed. Problem is syntax around
working code SET stmnt. I get error 'incorrect syntax near '+' .
Pls. advise.
BATCH FILE
@echo off
FOR /F "tokens=* delims= " %%F IN ('dir /b c:\scripts\*.pdf') DO (
set newstring=%%F
REM set newString=%myparam:~0,-1%
goto tests
)
:tests
SQLCMD -E -S US34002\GRIZZ -E -Q "EXEC LotPkt.dbo.sp_bind @myparam1=N'%newstring%'"
-------------------------------------------------------------
STORED PROC
USE [LotPkt]
GO
/****** Object: StoredProcedure [dbo].[sp_bind] Script Date: 10/3/2014 2:36:20 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_bind] @myparam1 varchar(100)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @rt as varchar (20)
declare @po as varchar (20)
declare @newstring as varchar (50)
DECLARE @FPathvarchar(500)
declare @sql as varchar (500)
select @rt = coalesce(rtno, '?') from rt124190 where verifiedby = 'we'
select @po = coalesce(posono, '?') from rt124190 where verifiedby = 'we'
print @myparam1
--this line works to add new blob to tbl, but uses hard-coded values for column results
SET @sql = 'INSERT TestBlob (tbname, tbRTNo, tbPOSONo, tbBin) SELECT 1, 2, 3, BulkColumn FROM OPENROWSET(Bulk '''+ @FPath +''', SINGLE_BLOB) AS tb'
--working code-how to use variables in Select stmnt to populate blob tbl?
SET @sql = 'INSERT INTO TestBlob (tbname, tbRTNo, tbPOSONo, tbBin) SELECT '''+@myparam1+''' AS tbname + '''+@rt+''' AS tbRTNo + '''+@po+''' AS tbPOSONo, BulkColumn FROM OPENROWSET(Bulk '''+ @FPath +''', SINGLE_BLOB) AS tb'
EXEC (@sql)
END
October 7, 2014 at 12:38 pm
Just figured out the answer.
I did not separate each variable using a comma in the Select stmnt.
SET @sql = 'INSERT INTO TestBlob (tbname, tbRTNo, tbPOSONo, tbBin) SELECT '''+@myparam1+''' AS tbname, '''+@rt+''' AS tbRTNo, '''+@po+''' AS tbPOSONo, BulkColumn FROM OPENROWSET(Bulk '''+ @FPath +''', SINGLE_BLOB) AS tb'
EXEC (@sql)
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply