none
How to create a stored procedure to insert data in the table

    Question

  • Hi I had a question regarding the sql sp here is the procedure to insert into table.But how do i verify the input data is already in there or input data is null?

    CREATE PROCEDURE Type @Type nvarchar(100)

    ---if((select COUNT(*)from dbo.tblMytable where Column=@Type)>0)

    Insert into dbo.tblMytable

    Values(@Type)

    else 

    do....?

     

    How can I verify that?

     

    Thanks in advace,


    satwick
    Tuesday, November 08, 2011 9:56 PM

Answers

  • Hi Sat !

    Here is the sample snippet;


    CREATE PROCEDURE usp_Type @Type NVARCHAR(100)
    AS
    BEGIN 
    
    IF NOT EXISTS(SELECT 1 FROM dbo.tblMyTable WHERE [Column] = @Type OR @Type IS NULL)
    
     INSERT INTO dbo.tblMyTable 
     VALUES(@Type) 
    
    END
    
    

     

    Please let me know if this doesn’t work for you. Hope I have answered you correctly.

    Thanks,
    Hasham

    • Marked as answer by satwick Tuesday, November 08, 2011 10:55 PM
    Tuesday, November 08, 2011 10:05 PM

All replies

  • Hi Sat !

    Here is the sample snippet;


    CREATE PROCEDURE usp_Type @Type NVARCHAR(100)
    AS
    BEGIN 
    
    IF NOT EXISTS(SELECT 1 FROM dbo.tblMyTable WHERE [Column] = @Type OR @Type IS NULL)
    
     INSERT INTO dbo.tblMyTable 
     VALUES(@Type) 
    
    END
    
    

     

    Please let me know if this doesn’t work for you. Hope I have answered you correctly.

    Thanks,
    Hasham

    • Marked as answer by satwick Tuesday, November 08, 2011 10:55 PM
    Tuesday, November 08, 2011 10:05 PM
  • Thanks man it worked well
    satwick
    Tuesday, November 08, 2011 10:54 PM
  • You need to catch up on your SQL. Even tho it is a skeleton, try  to use proper data element names. There is no such thing as a magical, generic type, etc.  Most SQL Programmers would use 
    IF EXISTS 
       (SELECT * FROM Foobar WHERE foobar_type = @@in_somethign_typesomething_type)
    Then you need to stop thinking in procedural code and start writing real SQL, which is declarative. The use of COUNT(*) shows that you think of a table as rows and not as a whole unit of work.
    Your request does not show the key for the rows you want to insert! And the new somethgin_type cannot be a key because of duplication.  So this makes no sense
    We can use the MERGE statement to do all of this checking and be to be sure that only unique values of the something_type are inserted. 
    CREATE PROCEDURE Insert_something_type
     (@in_something_type NVARCHAR(100))
    MERGE INTO Foobar
    USING (SELECT i FROM (VALUES(@in_something_type)) AS X(i))
    ON foobar_type = @in_something_type
    WHEN NOT MATCHED 
    THEN INSERT VALUES(@in_something_type);
    This si why we alwasy ask that uyou post DDL instead of narratives. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL
    Wednesday, November 09, 2011 5:17 AM