How to used ' Character in Sql query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to used ' Character in Sql query

Author

Topic

yaman
Posting Yak Master

213 Posts

Posted - 2008-05-02 : 03:47:00

begin
SET @STR='INSERT INTO tbl_Level1' + '(' + @columnName + ',orgID' + ')' + 'Values' + '(' + @Level1Name + ',' + @orgID + ')'

EXEC @STR

ERROR :- Conversion failed when converting the varchar value 'INSERT INTO tbl_Level1 (Level1Name_HIN,orgID ) Values (@Level1Name,' to data type int. pls help me out

Yaman

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-02 : 03:53:13

Whats the passed value for @orgID?

yaman
Posting Yak Master

213 Posts

Posted - 2008-05-02 : 03:56:36

EXEC @return_value = [dbo].[stp_Level1Add]
@Level1Name = N'f',
@LanguageID = 1,
@OrgID = 1,
@Level1ID = @Level1ID OUTPUT

Yaman

yaman
Posting Yak Master

213 Posts

Posted - 2008-05-02 : 04:03:43

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER proc [dbo].[stp_Level1Add]
@Level1Name varchar(100),
@LanguageID int,
@OrgID int,
@Level1ID int OUTPUT
as

Declare @LanguagePostfix varchar(5),@str VARCHAR(400)
SET @STR='INSERT INTO tbl_Level1'
set @LanguagePostfix = (select Postfix from tbl_Language where langID = @LanguageID)

Declare @columnName varchar(20)
set @columnName = 'Level1Name_' + @LanguagePostfix
print @columnName
--print @columnname
if not exists(select * from tbl_Level1 where @columnName=@Level1Name and orgID=@OrgID)
begin
SET @STR='INSERT INTO tbl_Level1' + '(' + @columnName + ',orgID' + ')' + 'Values' + '(' +@Level1Name+ ',' + @orgID + ')'
EXEC @STR

SELECT @Level1ID=SCOPE_IDENTITY()

end

Yaman

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-02 : 04:47:28

What are types of Level1Name_ columns?

yaman
Posting Yak Master

213 Posts

Posted - 2008-05-02 : 05:40:09

nvarchar(100)

Yaman

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-02 : 05:56:22

quote:
Originally posted by yaman

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER proc [dbo].[stp_Level1Add]
@Level1Name varchar(100),
@LanguageID int,
@OrgID int,
@Level1ID int OUTPUT
as

Declare @LanguagePostfix varchar(5),@str VARCHAR(400)
SET @STR='INSERT INTO tbl_Level1'
set @LanguagePostfix = (select Postfix from tbl_Language where langID = @LanguageID)

Declare @columnName varchar(20)
set @columnName = 'Level1Name_' + @LanguagePostfix
print @columnName
--print @columnname
if not exists(select * from tbl_Level1 where @columnName=@Level1Name and orgID=@OrgID)
begin
SET @STR='INSERT INTO tbl_Level1' + '(' + @columnName + ',orgID' + ')' + 'Values' + '(' +@Level1Name+ ',' + CAST(@orgID AS varchar(5)) + ')'
EXEC @STR

SELECT @Level1ID=SCOPE_IDENTITY()

end

Yaman

Try changing sql string like this

yaman
Posting Yak Master

213 Posts

Posted - 2008-05-02 : 11:16:39

Sir ,
Still Error is Coming

Msg 2812, Level 16, State 62, Procedure stp_Level1Add, Line 20
Could not find stored procedure 'INSERT INTO tbl_Level1(Level1Name_HIN,orgID)Values(assa,1)'.

Yaman

karuna
Aged Yak Warrior

582 Posts

Posted - 2008-05-02 : 12:33:03

try this


SET @STR='INSERT INTO tbl_Level1' + '(' + @columnName + ',orgID' + ')' + 'Values' + '(''' +@Level1Name+ ''',' + CAST(@orgID AS varchar(5)) + ')'

Thanks
Karunakaran

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-05-03 : 02:58:48

Before execution run this and see what it returns

print @STR

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-03 : 06:10:16

quote:
Originally posted by yaman

Sir ,
Still Error is Coming

Msg 2812, Level 16, State 62, Procedure stp_Level1Add, Line 20
Could not find stored procedure 'INSERT INTO tbl_Level1(Level1Name_HIN,orgID)Values(assa,1)'.

Yaman

Put braces around EXEC

EXEC (@STR)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources