This is my stored procedure:

ALTER PROCEDURE [dbo].[Addentry]( 
               @entHead VARCHAR(50)  = NULL, 
               @entBody VARCHAR(2000)  = NULL, 
               @entType SYSNAME, 
               @entuID  INT  = 0) 
AS 
  BEGIN 
    SET @entType = Rtrim(@entType) 
     
    SET @entuID = Rtrim(@entuID) 
     
    DECLARE  @cmd  AS NVARCHAR(MAX) 
     
    SET @cmd = N'INSERT INTO ' + Quotename(@entType) + N' (uID, Heading, Body, pTime) VALUES ' + (@entuID + ', ' + @entHead + ', ' + @entBody + ', ' + Getutcdate()) 
     
    EXEC Sp_executesql 
      @cmd 
     
    IF @@ERROR <> 0 
      RAISERROR ('Post was not added',16,1) 
  END 

RETURN  

The error I get is this: Conversion failed when converting datetime from character string

I guess it has something to do with turning the getutcdate() into a string and then back to datetime. Is there another way around this?
Any suggestions?

Sincerely
Niklas Kihl

I must admit, one the reasons I dislike dynamic sql is that it can be really, really hard to debug...

What appears to be happening is that you do not have getUtcDate() included within the quotes and so it is actually running, not as part of the Insert, but as part of the Alter ...

VALUES '+(@entuID+', '+@entHead+', '+ @entBody+', +getutcdate())'  EXEC sp_executesql @cmd  

ie - move the quotes to put the getUtcDate() within them, rather than outside of them...

Hope that helps,

m

 Can you explain the reason of using Dynamic SQL in your case and not a direct insert command? In your case you just need to add ' to surround date.

 I'd be just as glad without a dynamic SQL but I can't seem to get the "QUOTENAME(@entType)" to work then. If you can help me write the INSERT statement without dynamic SQL I'd be just as happy =)

I tried moving the '' around like u said but nothing seemed to help....got another error though...

error: Conversion failed when converting the nvarchar value 'INSERT INTO [Articles] (uID, Heading, Body, pTime) VALUES (' to data type int. at System.Data.SqlClient.SqlConnection.OnError(SqlExce...

Thanks in advance!

Niklas Kihl

 How many possible EventTypes you may have?

If only a few, then just try

IF @EventType = 'MyFirstType'

   insert into MyFirstType ...

ELSE IF @EventType = 'MySecondType'

   ...

etc.

Otherwise you need to look into the syntax of sp_ExecuteSQL with attention and pass parameters definition there.

 Here is a sample explaining use of sp_ExecuteSQL

http://www.universalthread.com/Report.aspx?Session=3058755767684D593468413D206136543265496349324631794F77764A774644784E6F4B756E61673763776354 (I hope that this link is accessible)

Ahh, that works just perfect.

Thank you Naom!