Auto generate Param. Inserts inside sql server?

This is probably a really naive question, but I need to ask it.

Is there a way to automaticallly generate an INSERT statement inside sql
server with the associated entry parameters?

I have attempted to use query analyzaer to spit out a SQL SCRIPT which seems
to partly do the job of creating an INSERT based on the underlying
table...however, I far from happy with it as it:

1 Ignores Parameters
2.Includes a bunch of [] and <> around the fields and values which makes it
difficult to adapt...

Is there an easier way to create paramatized inserts without having to
rewrite from scratch?

Thanks in advance
Jason

Re: Auto generate Param. Inserts inside sql server?

jason@catamaranco.com wrote:
> This is probably a really naive question, but I need to ask it.
>
> Is there a way to automaticallly generate an INSERT statement inside
> sql server with the associated entry parameters?
>
> I have attempted to use query analyzaer to spit out a SQL SCRIPT
> which seems to partly do the job of creating an INSERT based on the
> underlying table...however, I far from happy with it as it:
>
> 1 Ignores Parameters
> 2.Includes a bunch of [] and <> around the fields and values which
> makes it difficult to adapt...
>
> Is there an easier way to create paramatized inserts without having to
> rewrite from scratch?
>
> Thanks in advance
> Jason

Do you mean a stored procedure generator?

I have a program called Embarcardero RapidSQL that does this. See below for
an example of its output. There may be freeware/shareware programs out there
to do the same thing (have you tried google?). Alternatively, you could
write something yourself.

Bob Barrows


************************************************************ ***********************
/*
* File Name:
* Description: Insert Procedure for the table "dbo.Orders"
* Created: 7/8/2005 at 10:05:50.946 by Embarcadero Rapid SQL 7.2
*/

CREATE PROCEDURE Orders_INS
(
@OrderID int,
@CustomerID nchar(5),
@EmployeeID int,
@OrderDate datetime
)
AS
BEGIN
BEGIN TRAN
INSERT INTO dbo.Orders (
OrderID,
CustomerID,
EmployeeID,
OrderDate)
VALUES
(
@OrderID,
@CustomerID,
@EmployeeID,
@OrderDate
)

IF (@@error!=0)
BEGIN
RAISERROR 20000 'Orders_INS: Cannot insert data into Orders_INS '
ROLLBACK TRAN
RETURN(1)
END

COMMIT TRAN
END
go

IF OBJECT_ID('Orders_INS') IS NOT NULL
PRINT '<<< CREATED PROCEDURE Orders_INS >>>'
ELSE
PRINT '<<< FAILED CREATING PROCEDURE Orders_INS >>>'
************************************************************ ********************

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: Auto generate Param. Inserts inside sql server?

> Do you mean a stored procedure generator?

Hi Bob- I think so :)

I am not an expert on your 'transactional' sp you spat out there but
basically, If I wanted to create this paramatized query based on the
underlying table...would your program do it ...and do it easily and
simply?...is there anything wrong with my logic or train of thought of
here.....?


CREATE Procedure spr_AddStory

@StoryTitle varchar(100),
@StoryURL varchar(150),
@StoryBlurb varchar(1200),
@StoryBrokerID int,
@StoryCompanyID int,
@StoryCategoryID int,
@StoryDeptID int,
@StoryKeyword1 varchar(50),
@StoryKeyword2 varchar(50),
@StoryKeyword3 varchar(50),
@RelatedURL1 varchar(150),
@RelatedURL2 varchar(150),
@RelatedURL3 varchar(150),
@StoryDate datetime,
@StoryImageURL varchar(150),
@StoryBLN int


AS

INSERT INTO Story (StoryTitle, StoryURL, StoryBlurb, StoryBrokerID,
StoryCompanyID, StoryCategoryID, StoryDeptID, StoryKeyword1, StoryKeyword2,
StoryKeyword3, RelatedURL1, RelatedURL2, RelatedURL3, StoryDate,
StoryImageURL, StoryBLN)
VALUES
(@StoryTitle,@StoryURL,@StoryBlurb,@StoryBrokerID,@StoryComp anyID,@StoryCategoryID,@StoryDeptID,@StoryKeyword1,@StoryKey word2,@StoryKeyword3,
@RelatedURL1,@RelatedURL2,@RelatedURL3,@StoryDate,@StoryImag eURL,@StoryBLN)

GO

Re: Auto generate Param. Inserts inside sql server?

jason@catamaranco.com wrote:
>> Do you mean a stored procedure generator?
>
> Hi Bob- I think so :)
>
> I am not an expert on your 'transactional' sp you spat out there but
> basically, If I wanted to create this paramatized query based on the
> underlying table...would your program do it ...and do it easily and
> simply?...

Yes. it took ~20 sec to generate that procedure. Right-click a table, select
Generate>Procedure>Insert,
select the columns I want from the ensuing dialog, click OK, and voila.

Just delete the stuff you don't want.

If I choose to gnerate a statement instead of a procedure, I get this:

INSERT INTO dbo.Orders
(CustomerID,
EmployeeID,
OrderDate,
RequiredDate,
ShippedDate)
VALUES
(/* CustomerID_value = nchar(5) */,
/* EmployeeID_value = int */,
/* OrderDate_value = datetime */,
/* RequiredDate_value = datetime */,
/* ShippedDate_value = datetime */)

There's also a Code Generation Facility tool that provides a few more
options (such as assigning permissions ... )

Caveat: RapidSQL is not cheap. I've barely scratched the surface of its
capabilities. Again, there may be other, more affordable tools out there
that do just this function (stored procedure generation). Do a google search
or ask on a sql server newsgroup for recommendations.

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: Auto generate Param. Inserts inside sql server?

Ok thanks Bob...I'll investigate some of the freeware and possibly get back
to you on the RapidSQL.

- Jason

"Bob Barrows [MVP]" wrote in message
news:%2358K7u8gFHA.3312@TK2MSFTNGP10.phx.gbl...
> jason@catamaranco.com wrote:
>>> Do you mean a stored procedure generator?
>>
>> Hi Bob- I think so :)
>>
>> I am not an expert on your 'transactional' sp you spat out there but
>> basically, If I wanted to create this paramatized query based on the
>> underlying table...would your program do it ...and do it easily and
>> simply?...
>
> Yes. it took ~20 sec to generate that procedure. Right-click a table,
> select Generate>Procedure>Insert,
> select the columns I want from the ensuing dialog, click OK, and voila.
>
> Just delete the stuff you don't want.
>
> If I choose to gnerate a statement instead of a procedure, I get this:
>
> INSERT INTO dbo.Orders
> (CustomerID,
> EmployeeID,
> OrderDate,
> RequiredDate,
> ShippedDate)
> VALUES
> (/* CustomerID_value = nchar(5) */,
> /* EmployeeID_value = int */,
> /* OrderDate_value = datetime */,
> /* RequiredDate_value = datetime */,
> /* ShippedDate_value = datetime */)
>
> There's also a Code Generation Facility tool that provides a few more
> options (such as assigning permissions ... )
>
> Caveat: RapidSQL is not cheap. I've barely scratched the surface of its
> capabilities. Again, there may be other, more affordable tools out there
> that do just this function (stored procedure generation). Do a google
> search or ask on a sql server newsgroup for recommendations.
>
> Bob Barrows
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>