I want to create a
registration form using "stored procedure" in asp.net with " table valued paramter"
and that sp also checks a username exists or not
and code is
USE [news]
GO
CREATE TYPE [dbo].[SampleDataType2] As Table
(
Fname_Name varchar(50),
Lname_Name varchar(50),
USER_NAME nvarchar(50),
User_email varchar(50),
User_mobile varchar(50),
User_Permanent_Address varchar(50),
User_Contact_Address varchar(50),
User_DOB varchar(50),
User_image varchar(50)
)
/****** Object: StoredProcedure [dbo].[Regitsration] Script Date: 02/09/2014 01:05:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [dbo].[Regitsration1]
@Sample As [dbo].[SampleDataType2] Readonly,
@Username nvarchar(50),
@Result varchar(50) OUTPUT
As
Begin
IF EXISTS(SELECT User_Name FROM Registration_master WHERE User_Name = @Username)
Begin
Set @Result = 'Already Exist'
Return;
End
Else
Begin
Insert into Registration_master (Fname_Name,
Lname_Name,
USER_NAME,
User_email,
User_mobile,
User_Permanent_Address,
User_Contact_Address,
User_mobile,
User_image)
Set @Result = 'Done'
Return;
End
End
and problem is: Procedure Regitsration1, Line 24
Incorrect syntax near the keyword 'Set'.
thanks..
Loading

Biswa Pujarini MohapatraPosted Feb 9, 2014, 1:03 AM
Aditya rajPosted Feb 17, 2021, 10:28 AM
Jignesh TrivediPosted Feb 10, 2014, 1:31 AM
Hi,
Agree with Biswa.
With the insert statement you have to pass the value
Insert into table1 (id , name)
select id, name from table2
hope this will help you.
Biswa Pujarini MohapatraPosted Feb 9, 2014, 12:36 PM
Sharad GuptaPosted Feb 8, 2014, 11:54 PM
But i d'nt want parameter inside sp bcoz i d'nt want use prameter in my C# code.
SUNIL GUTTAPosted Feb 8, 2014, 4:44 PM
You just missed insert syntax ...
Anyways i done for you . which will work fine .
USE [sunildb]
GO
/****** Object: StoredProcedure [dbo].[Regitsration1] Script Date: 02/09/2014 03:05:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Regitsration1]
@Sample As [dbo].[SampleDataType2] Readonly,
@Username nvarchar(50),
@Fname_Name nvarchar(50),
@Lname_Name nvarchar(50),
@User_email nvarchar(50),
@User_mobile int,
@User_Permanent_Address nvarchar(50),
@User_Contact_Address nvarchar(50),
@User_image image ,
@Result varchar(50) OUTPUT
As
Begin
IF EXISTS(SELECT User_Name FROM Registration_master WHERE User_Name = @Username)
Begin
Set @Result = 'Already Exist'
Return;
End
Else
Begin
Insert into Registration_master (Fname_Name,
Lname_Name,
USER_NAME,
User_email,
User_mobile,
User_Permanent_Address,
User_Contact_Address,
User_mobile,
User_image)
values (@Fname_Name,
@Lname_Name,
@Username,
@User_email,
@User_mobile,
@User_Permanent_Address,
@User_Contact_Address,
@User_image)
set @Result='done'
return;
end
end
-----------------------------------Mark answer if it helps--------------------------------