October 9, 2009 at 2:04 pm
1)Please code up a stored procedure that takes a single parameter and allows the calling application to insert multiple records into a single table
October 9, 2009 at 2:23 pm
kaushikrsharma (10/9/2009)
1)Please code up a stored procedure that takes a single parameter and allows the calling application to insert multiple records into a single table
-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:<Author,,Name>
-- Create date: <Create Date,,>
-- Description:<Description,,>
-- =============================================
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>
-- Add the parameters for the stored procedure here
<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,
<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
GO
October 9, 2009 at 2:25 pm
CREATE PROCEDURE dbo.help_friend_with_homework (@NumberOfRecords INT)
AS
BEGIN
SET NOCOUNT ON
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'CHEATTABLE')
CREATE TABLE CHEATTABLE (id INT, name varchar(50));
WITH i_copied_this_from_the_internet_CTE AS
( SELECT row_number() OVER (ORDER BY id) as RowNum, name
FROM sys.sysobjects)
INSERT INTO CHEATTABLE SELECT * FROM i_copied_this_from_the_internet_CTE WHERE rownum <= @NumberOfRecords
SELECT id, name FROM CHEATTABLE
END
GO
EXEC dbo.help_friend_with_homework @NumberOfRecords = 10
Be Careful!
October 9, 2009 at 5:57 pm
abair34 (10/9/2009)
CREATE PROCEDURE dbo.help_friend_with_homework (@NumberOfRecords INT)AS
BEGIN
SET NOCOUNT ON
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'CHEATTABLE')
CREATE TABLE CHEATTABLE (id INT, name varchar(50));
WITH i_copied_this_from_the_internet_CTE AS
( SELECT row_number() OVER (ORDER BY id) as RowNum, name
FROM sys.sysobjects)
INSERT INTO CHEATTABLE SELECT * FROM i_copied_this_from_the_internet_CTE WHERE rownum <= @NumberOfRecords
SELECT id, name FROM CHEATTABLE
END
GO
EXEC dbo.help_friend_with_homework @NumberOfRecords = 10
Be Careful!
FYI, you're missing the ";" in front of the "WITH i_copied_this_from_the_internet_CTE AS", but otherwise very accurate analysis of the issue
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 9, 2009 at 8:56 pm
Hint: XML.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 9, 2009 at 9:31 pm
So its time for school work again..
CEWII
October 10, 2009 at 5:42 am
kaushikrsharma (10/9/2009)
1)Please code up a stored procedure that takes a single parameter and allows the calling application to insert multiple records into a single table
create proc t @val varchar(10)
as
insert into relevant_table values(@val)
Of course, this would only work under certain conditions which are unknown to me since you decided not to fully describe the requirement.
So I made the following assumptions:
allows the calling application to insert multiple records
= the app will call the proc multiple times, once per record.
that takes a single parameter
= the parameter is the one that's supposed to be inserted.
...single parameter ... insert ... into a single table
= the table has only one column where the parameter will be inserted into.
October 10, 2009 at 6:12 am
WayneS (10/9/2009)
FYI, you're missing the ";" in front of the "WITH i_copied_this_from_the_internet_CTE AS"
He's not. Look at the statement preceding the CTE. It's terminated with a ;
The requirement for a CTE is not that it must start with a ;, it's that the previous statement must be terminated with a ;. Because that's not common (yet) the examples tend to show the ; at the beginning of the CTE, just to be sure.
These examples all work and are all completely synonymous with each other
SELECT Count(*) FROM sys.tables;
WITH Example AS (select name, type from sys.objects)
select * from Example WHERE type = 'U';
SELECT Count(*) FROM sys.tables
;
WITH Example AS (select name, type from sys.objects)
select * from Example WHERE type = 'U';
SELECT Count(*) FROM sys.tables
;WITH Example AS (select name, type from sys.objects)
select * from Example WHERE type = 'U';
SELECT Count(*) FROM sys.tables;WITH Example AS (select name, type from sys.objects) select * from Example WHERE type = 'U';
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply