Creating SQL Server Stored Procedures in Deployment

Hey guys,

I'm having a problem. I've been given a task to complete. I was given a database, and asked to wrap a website around it with certain functionality. I did this, and added seven stored procedures in the proccess. Everything works, Business Logic Layer, Data Access Layer, error validation, even screwed around with SQL-injection protection. Lovely, yes?

However, when my little website gets tested, it's going to be plugged into a fresh database - the exact same database I was given, only with different data in the tables. My stored procedures won't be in that database. I can detect if my stored procedure doesn't exist easily enough by catching the error at runtime and checking the code.

I would like to create the stored procedure inside that catch block. I just don't know how.

The easy answer is just to use embedded SQL in my application instead of stored procedures. This isn't a commercial application, it's just a task I've been given to test my abilities. But embedded SQL is icky. I'd rather do it properly.

With the help of the executenonquery u may create the stored procedures which is not the best way. stored procedures,functions or triggers creation means generally people will implement in database not through front end. Not only in this case u can perform ddl commands through executenonquery but max no one will prefer.

Thank u

Baba

Please remember to click "Mark as Answer" on this post if it helped you.

Creating new tables for new users

hi all,

How can i create a new data table automatically for new users signing into the website......what i mean here is that i have a predefined database table and i want that users signing in can have that table for them unique , so that they can fill data for themselves and that data will be visible to all just like forums

Hello Pankaj,

You can instantiate a new DataTable in the session where the user can modify his data.

But another possibility is to add a column like userid or username to the predefined table and write business logic that allows the user to modify its own records (where his username matches the username in the records) and other users can only read the records.

In this case a user is owner of his records (has it unique to himself for updates).

Jeroen Molenaar.

Creating join statements

I have the following situation. We sell books on our website, and some
of the books have more than one author. So I needed to create a
many-to-many table, which is the intermidiate table between the author
table and the book table.

I can't get the right join statement to work. I've used the code below,
submitting an isbn (book id number) to identify the book, but the return
from the query simply sends me back all of the authors that are in the
many_to_many table
(called the book_to_author table here). I'd like it to return only the
authors attached to that isbn, instead of all the authors that are in
that table. What's wrong with the code below? Thanks for your help!

SELECT a.firstname, a.lastname, a.title AS degree, a.bio, a.author_id,
bf.isbn, bf.title AS booktitle, m.isbn AS Expr2, m.id, m.author_id AS
Expr3 FROM author a INNER JOIN book_to_author m ON
a.author_id=m.author_id CROSS JOIN book_detail_final bf WHERE
bf.isbn='"&isbn&"' order by m.id desc

Bill

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Do the following:
Your inner join statement should be:INNER JOIN book_to_author m ON
a.author_id= book_to_author.author_id

--
__________________________________________________ _________________
Remotely manage MS SQL db with SQLdirector -
www.ciquery.com/tools/sqldirector/

"Bill" <BillZimmerman@.gospellight.com> wrote in message
news:3f7b2243$0$24217$75868355@.news.frii.net...
> I have the following situation. We sell books on our website, and some
> of the books have more than one author. So I needed to create a
> many-to-many table, which is the intermidiate table between the author
> table and the book table.
> I can't get the right join statement to work. I've used the code below,
> submitting an isbn (book id number) to identify the book, but the return
> from the query simply sends me back all of the authors that are in the
> many_to_many table
> (called the book_to_author table here). I'd like it to return only the
> authors attached to that isbn, instead of all the authors that are in
> that table. What's wrong with the code below? Thanks for your help!
> SELECT a.firstname, a.lastname, a.title AS degree, a.bio, a.author_id,
> bf.isbn, bf.title AS booktitle, m.isbn AS Expr2, m.id, m.author_id AS
> Expr3 FROM author a INNER JOIN book_to_author m ON
> a.author_id=m.author_id CROSS JOIN book_detail_final bf WHERE
> bf.isbn='"&isbn&"' order by m.id desc
> Bill
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||Bill (BillZimmerman@.gospellight.com) writes:
> I have the following situation. We sell books on our website, and some
> of the books have more than one author. So I needed to create a
> many-to-many table, which is the intermidiate table between the author
> table and the book table.
> I can't get the right join statement to work. I've used the code below,
> submitting an isbn (book id number) to identify the book, but the return
> from the query simply sends me back all of the authors that are in the
> many_to_many table
> (called the book_to_author table here). I'd like it to return only the
> authors attached to that isbn, instead of all the authors that are in
> that table. What's wrong with the code below? Thanks for your help!
> SELECT a.firstname, a.lastname, a.title AS degree, a.bio, a.author_id,
> bf.isbn, bf.title AS booktitle, m.isbn AS Expr2, m.id, m.author_id AS
> Expr3 FROM author a INNER JOIN book_to_author m ON
> a.author_id=m.author_id CROSS JOIN book_detail_final bf WHERE
> bf.isbn='"&isbn&"' order by m.id desc

That CROSS JOIN is most certainly not what you like. (author JOIN
book_to_author) gives your all authors and the books they have
written. Then you form a cartesian product with the book_detail_final
table, and finally you restrict the book with that isbn. But there
is no connection with the first two tables.

It is possible that this query cuts it:

SELECT a.firstname, a.lastname, a.title AS degree, a.bio, a.author_id,
bf.isbn, bf.title AS booktitle, m.isbn AS Expr2, m.id,
m.author_id AS Expr3
FROM author a
JOIN book_to_author m ON a.author_id = m.author_id
JOIN book_detail_final bf ON bf.isbn = m.isbn
WHERE bf.isbn='"&isbn&"'
ORDER BY m.id DESC

But since I don't know your tables, this is a guess.

If the guess is wrong, please post the following:

o CREATE TABLE statements of your tables.
o INSERT statements with sample data.
o The desired given from the sample data.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp