Click here to Skip to main content
15,041,525 members
Please Sign up or sign in to vote.
4.00/5 (2 votes)
See more:
Hi I want to create a stored procedure that takes specific values from one table and inserts them into another if a specific table been 'DateAquired ' is null I have this stored procedure below but it doesn't seem as expected please help.
Thanks in advance

this is my code

SQL
USE [MediaPlayer]
GO
/****** Object:  StoredProcedure [dbo].[sp_Wishlists]    Script Date: 11/26/2013 11:43:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[sp_Wishlists]
	-- Add the parameters for the stored procedure here
	@Name nvarchar (250),
	@FileName nvarchar (250),
	@FilePath  nvarchar (50),
	@FileSize float,
	@DateAdded date,
	@MediaLength nvarchar (50),
	@MediaSubType nvarchar(50),
	@MediaType nvarchar(50),
	@Thumbnail image,
	@DateAquired nvarchar (50),
	@WishList int output ,
	@CName nvarchar  (50)output ,
	@Media nvarchar (50)output,
	@WishListID int output
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
Select GeneralID, Name, FileName, FilePath,FileSize,DateAdded,MediaLength,MediaSubType,MediaType, Thumbnail,DateAquired As Wishlist 
From General where NULLIF(DateAquired,'')IS Null

insert into Wishlists (generalID ,MediaType, Name)
values ((IDENT_CURRENT('dbo.General')),(IDENT_CURRENT('dbo.General')),(IDENT_CURRENT('dbo.General')))
SET @WishListID = @@IDENTITY
select GeneralID, MediaSubType, Name

From General where NOT EXISTS (Select Name from WishLists Where Name =@Name);
END
Posted
Comments
CHill60 26-Nov-13 7:37am
   
What do you mean "doesn't seem as expected" - I presume you get an error when you run this - what is it?
Member 10423955 26-Nov-13 7:54am
   
Well it just insert nulls into my wishlist table instead of values
CHill60 26-Nov-13 8:01am
   
See RyanDevs solution
Herman<T>.Instance 26-Nov-13 7:42am
   
no values, no set between Insert Into and Select
CHill60 26-Nov-13 7:47am
   
You need to use the reply button next to a post otherwise the poster will not be notified of your comment. I've never seen an error message worded like that - please be specific in future. Not sure what the point of all those parameters is, they're not being used. The only insert in your SP is just trying to insert that ID three times. See RyanDev's solution for how to do it properly *smacking myself in head*
Herman<T>.Instance 26-Nov-13 7:52am
   
FYI: it is not my SP!
CHill60 26-Nov-13 7:55am
   
D'oh - sorry!
Member 10423955 26-Nov-13 7:53am
   
well what I'm trying to achieve by this ,is to create an automatic output where I don't have to fill in any values into my wish list table yet whenever I insert something in general it should just automatically insert them into my wish list

An easy way to insert values from one table into another is to use INSERT INTO SELECT

SQL
INSERT INTO Table1(Field1, Field2, Field3)
SELECT Field1, Field2, Field3
FROM Table2
WHERE field4 = 'Whatever'
   
Comments
kabifarm 26-Nov-13 7:54am
   
you can also do it thiis way

On the same databse use:
SELECT * into stock_record1
FROM stock_record

in another database
SELECT * into [pos].[dbo].[bank]

from [school].[dbo].[bank]
CHill60 26-Nov-13 7:55am
   
But not if the table already exists
ZurdoDev 26-Nov-13 7:57am
   
As CHill60 points out, this will actually create the table. Which is very useful sometimes but if the table already exists it won't work this way.
It is not clear from the code which table are you taking values from and inserting to which one. All I can see is one row of identity inserts.
   
Comments
phil.o 26-Nov-13 8:02am
   
Please do not post a comment as a solution.
You should copy your answer, post it as a comment to the OP's question, and delete this solution.
I solved thanks for the help
SQL
USE [MediaPlayer]
GO
/****** Object:  StoredProcedure [dbo].[sp_Wishlists]    Script Date: 11/26/2013 11:43:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<author,,name>
-- Create date: <create>
-- Description:	<description,,>
-- =============================================
ALTER PROCEDURE [dbo].[sp_Wishlists]
	-- Add the parameters for the stored procedure here
	@Name nvarchar (250),
	@FileName nvarchar (250),
	@FilePath  nvarchar (50),
	@FileSize float,
	@DateAdded date,
	@MediaLength nvarchar (50),
	@MediaSubType nvarchar(50),
	@MediaType nvarchar(50),
	@Thumbnail image,
	@DateAquired nvarchar (50),
	@WishList int output ,
	@CName nvarchar  (50)output ,
	@Media nvarchar (50)output,
	@WishListID int output
	
AS
BEGIN
SET NOCOUNT ON;
--inserting into this table
insert into Wishlists (generalID ,MediaType, Name)
values ((IDENT_CURRENT('dbo.General')),@MediaType, @Name)
SET @WishListID = @@IDENTITY
--Select from genereal Table
Select GeneralID, Name, FileName, FilePath,FileSize,DateAdded,MediaLength,MediaSubType,MediaType, Thumbnail,DateAquired As Wishlist 
From General where NULLIF(DateAquired,'')IS Null
-- Select from 
Select * from WishLists
select GeneralID, MediaSubType, Name
From General where NOT EXISTS (Select Name from WishLists Where Name =@Name);
END</create>
   

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900