syntax error in sql stored procedure

Question

1.40/5 (2 votes)

See more:

hii..

my problem in sql stores procedure

i want to pass @vquery1 in insert command.. @vquery1 is varchar(max),

SQL

USE [SCJ]
GO
/****** Object:  StoredProcedure [dbo].[spGetCHLDETAIL1]    Script Date: 03/20/2014 01:46:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<author,,name>
-- Create date: <create>
-- Description:	<description,,>
-- =============================================
ALTER PROCEDURE [dbo].[spGetCHLDETAIL1] 
	-- Add the parameters for the stored procedure here
	 @DistrictID VARCHAR(250),
     @barcode VARCHAR(50)
    
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE
	@ChildTblName AS VARCHAR (125), 
    @FamilyTblName AS VARCHAR (125), 
    @vquery1 AS varchar(max),
     @vQuery AS VARCHAR (max);
   
     
    SET @ChildTblName = '[' + @DistrictID + 'Child_D' + ']'
    SET @FamilyTblName = '[' + @DistrictID + 'Family_D' + ']'

    -- Insert statements for procedure here
	   SET @vQuery = 'SELECT Child.scanno,Child.cname,Child.distt,Child.block,Child.gpmctc,Child.village ,Child.habitation, Child.barcode,Child.sex,Child.dob,Family.pincode,Family.hhno,Family.fname,Family.mname,Family.gname,Family.social_grp,Family.minority,Family.total_male,Family.total_fema,Family.seasonal_m,Family.migration_,Family.duration,Family.lp,Family.up,Family.sec,Family.catch_s_lp,Family.catch_s_up,Family.catch_s_se,Child.location,
    Child.residing,Child.mother_ton,Child.disability,Child.sch_code,Child.class,Child.nereason,Child.droupout,Child.highclass
    FROM ' + @ChildTblName + ' As Child  WITH (NOLOCK) INNER JOIN ' + @FamilyTblName + ' AS Family WITH (NOLOCK) ON 
    Child.barcode=Family.barcode WHERE LTRIM(RTRIM(Child.cname)) <> '''' AND Child.distt = ' + @DistrictID + '';
   
    -- EXEC(@vQuery)
 
   -- exec(@vquery1)
  insert into CHLDINFO_LOG(Chld_Name) values(@vquery)

i will pass more parameter.. i m check for one value..

it's not working ..

please help.. it's urgent..

thanks in advance

Posted 20-Mar-14 0:56am

bindash

Updated 20-Mar-14 1:42am

Sibasisjena

v4

Add a Solution

Comments

Richard MacCutchan 20-Mar-14 7:00am

it's not working
And you expect us to guess what that means?

it's urgent.
no it's not.

King Fisher 20-Mar-14 7:21am

SET @vquery1='select Child.cname FROM ' + @ChildTblName + ' As Child INNER JOIN ' + @FamilyTblName + ' AS Family ON
Child.barcode=Family.barcode WHERE Child.distt = ' + @DistrictID + '';

you must pass value for those vaiables.bad one ;)

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sibasisjena · Answer 1 · 2014-03-20T00:59:00

Solution 2

SQL

--How to create the procedure
CREATE PROCEDURE [dbo].[InsertMyValue] 
@vquery1 VARCHAR(MAX)
AS 
BEGIN
insert into CHLDINFO_LOG(Chld_Name) values(@vquery1 )
END

--To execute the procedure
EXEC InsertMyValue 'ABC'

Posted 20-Mar-14 0:59am

Sibasisjena

Updated 20-Mar-14 1:01am

v3

Comments

bindash 20-Mar-14 7:11am

it's not working .. my query is

SET @vquery1='select Child.cname FROM ' + @ChildTblName + ' As Child INNER JOIN ' + @FamilyTblName + ' AS Family ON
Child.barcode=Family.barcode WHERE Child.distt = ' + @DistrictID + '';

Sibasisjena 20-Mar-14 7:14am

What do you want? you want to create a procedure or what ?
And what is the problem you are facing.

bindash 20-Mar-14 7:24am

my procedure is for display data in labels in web page..it's working.. the procedure has more field ex..child name,disstt,clock etc of 2 tables.. i want to store this data in a table childinfo_log

Sibasisjena 20-Mar-14 7:31am

you want a procedure which will insert multiple values into the table.
Am i correct ?

bindash 20-Mar-14 7:34am

yes my procedure is working for diasplay data.. i want to store this display data in a table

member86215483 · Answer 2 · 2014-04-03T00:27:00

hi. Try this. I think you left single inverted comma (') in where clause

USE [SCJ]
GO
/****** Object:  StoredProcedure [dbo].[spGetCHLDETAIL1]    Script Date: 03/20/2014 01:46:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<author,,name>
-- Create date: <create>
-- Description:	<description,,>
-- =============================================
ALTER PROCEDURE [dbo].[spGetCHLDETAIL1] 
	-- Add the parameters for the stored procedure here
	 @DistrictID VARCHAR(250),
     @barcode VARCHAR(50)
    
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE
	@ChildTblName AS VARCHAR (125), 
    @FamilyTblName AS VARCHAR (125), 
    @vquery1 AS varchar(max),
     @vQuery AS VARCHAR (max);
   
     
    SET @ChildTblName = '[' + @DistrictID + 'Child_D' + ']'
    SET @FamilyTblName = '[' + @DistrictID + 'Family_D' + ']'
 
    -- Insert statements for procedure here
	   SET @vQuery = 'SELECT Child.scanno,Child.cname,Child.distt,Child.block,Child.gpmctc,Child.village ,Child.habitation, Child.barcode,Child.sex,Child.dob,Family.pincode,Family.hhno,Family.fname,Family.mname,Family.gname,Family.social_grp,Family.minority,Family.total_male,Family.total_fema,Family.seasonal_m,Family.migration_,Family.duration,Family.lp,Family.up,Family.sec,Family.catch_s_lp,Family.catch_s_up,Family.catch_s_se,Child.location,
    Child.residing,Child.mother_ton,Child.disability,Child.sch_code,Child.class,Child.nereason,Child.droupout,Child.highclass
    FROM ' + @ChildTblName + ' As Child  WITH (NOLOCK) INNER JOIN ' + @FamilyTblName + ' AS Family WITH (NOLOCK) ON 
    Child.barcode=Family.barcode WHERE LTRIM(RTRIM(Child.cname)) <> '''' AND Child.distt = ''' + @DistrictID + '''';
   
    -- EXEC(@vQuery)
 


you can use print to check the statement
--print @vQuery

EXEC(@vQuery)


   -- exec(@vquery1)
  --insert into CHLDINFO_LOG(Chld_Name) values(@vquery)

ArunRajendra · Answer 3 · 2014-03-20T00:59:00

Solution 1

Don't call exec(@vquery1). Just call

SQL

insert into CHLDINFO_LOG(Chld_Name) values(@vquery1 )

Posted 20-Mar-14 0:59am

ArunRajendra

Comments

bindash 20-Mar-14 7:10am

it's not working...
my query is

SET @vquery1='select Child.cname FROM ' + @ChildTblName + ' As Child INNER JOIN ' + @FamilyTblName + ' AS Family ON
Child.barcode=Family.barcode WHERE Child.distt = ' + @DistrictID + '';

syntax error in sql stored procedure

3 solutions

Solution 2

Solution 3

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us