February 20, 2008 at 6:33 am
Hi,
Can some one tell me how I can pass a integer value to where clause in a stord procedure.
For Example
__________
ALTER PROCEDURE sp_Sample
(
@Empid nvarchar(100)
)
AS
Declare @STR varchar(1000)
Begin
set @STR = 'SELECT * from Employee WHERE empid=' + @Empid
Exec (@str)
end
Go
now when executing i want to get the empid to be like select * from employee where empid= 'A1234'
instead where am getting like empid = A1234
How can i get the single qoute there while executing....
Thanks,
February 20, 2008 at 7:16 am
First, why are you using dynamic sql here?
You can simply do this:
ALTER PROCEDURE sp_Sample
(
@Empid nvarchar(100)
)
AS
Begin
SELECT * from Employee WHERE empid= @Empid
end
Go
But, if there really is a reason to perform dynamic SQL you can do this:
ALTER PROCEDURE sp_Sample
(
@Empid nvarchar(100)
)
AS
Declare @STR varchar(1000)
Begin
set @STR = 'SELECT * from Employee WHERE empid=''' + @Empid + ''''
Exec (@str)
end
Go
The escape sequence to get a single quote is two single quotes ''. Then of course you have to wrap those with a single quote so that you end up with that mess of four single quotes at the end.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 20, 2008 at 7:32 am
Hi Grant,
Actualy, i was trying some thing where iam doing some bcp command operation.
Here is actual Question...Can you please let me know the Answer
ALTER Procedure BCP_Text_File
(
@table varchar(100),
@Cusip varchar(100),
@direction varchar(15),
@FileName varchar(100)
)
as
If exists(Select * from information_Schema.tables where table_name=@table)
Begin
--Declare @STR varchar(1000)
Declare @cmd varchar(1000)
set @cmd = 'bcp "Select LoanNo,PrepayVector1,DefaultVector1,RecoveryLag,LossSeverity from CurveTempDB.dbo.tLoanVectors where cusip=''' + @Cusip + ''' " '+@direction+' '+@FileName+' -T -c'
print @cmd
exec master..xp_cmdshell @cmd
--Exec(@str)
end
else
Select 'The table '+@table+' does not exist in the database'
Now here when i execute the bcp command in command prompt
bcp "Select LoanNo,PrepayVector1,DefaultVector1,RecoveryLag,LossSeverity from CurveTempDB.dbo.tLoanVectors where cusip='86359B6G1' " queryout C:\Sample.txt -T -c
am able to get the output file in the given path...
where as when i run this in query analyzer i am getting the error for the above stored procedure...
Error = [Microsoft][SQL Native Client]Unable to open BCP host data-file
Please help me in this...
Thanks
February 20, 2008 at 7:47 am
It's probably a permisssions issue. XP_commandshell is going to run under the security context of the sql server instance where as your command prompt is going to run under your own security settings. That's most likely the issue. You can check by getting the command to work perfectly, then, instead of generating it, simply run it through xp_commandshell. I suspect you'll get the same issue.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 20, 2008 at 9:04 am
Hello Grant,
actually now iam getting a diff error while executing the same in query analyzer and taking queryout am getting error as ...
Copy direction must be either 'in', 'out' or 'format'.
But if i take Out for direction its saying that
An error occurred while processing the command line.
Any idea of this.
Thanks,
February 20, 2008 at 9:25 am
Sorry. I'm not terribly familiar with bcp.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 20, 2008 at 9:45 am
I'm not a BCP expert - but you're not specifying database and server to run this against. Assuming you do that - I'm thinking you'll be back to needing to run it under queryout.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 20, 2008 at 11:57 am
Hi
Iam using sql server 2005 . I just want to know like why am getting that
An error occurred while processing the command line error
on giving the 'out' in bcp. as iam getting this error in command prompt also. Is there any way that i can solve this..
Thanks,
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply