Insert and Selectin same SP - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Insert and Selectin same SP

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-01-06 : 08:35:05

dfetrow410@hotmail.com writes "I am building a form builder that allows you to make dynamic forms. I am
trying to make pre-selected questions. One of them is: Choose the Store
nearest you.
So I add the question in the first insert and get the identity to add in the
FormCreator_QuestionOptions in the second table. Here is the part I can't
get. The store names are in the franchisees table and I need to copy them
into the FormCreator_QuestionOptions and put the declared values in to with
the same insert/select statement.

declare @QuestionIDas int
declare @ViewOrder as int
declare @OptionTextboxMode as nvarchar

INSERT INTO
FormCreator_Question
(Q_Name )
VALUES
(@Q_Name)

set @QuestionID= @@identity
set @ViewOrder = 999
set @OptionTextboxMode = 'TextBoxMode.SingleLine'

INSERT FormCreator_QuestionOptions (QuestionID, OptionText, ViewOrder,
OptionTextboxMode)
SELECT REPLACE(REPLACE(UnitName, '/ Store', ''), '/ SRU', '') + ', ' +
State AS OptionText
FROM franchisees
WHERE (Ctry = 'US')
ORDER BY State

--

Dave"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-01-06 : 08:55:35

just add the variables to the select
INSERT FormCreator_QuestionOptions (QuestionID, OptionText, ViewOrder,
OptionTextboxMode)
SELECT @QuestionID, REPLACE(REPLACE(UnitName, '/ Store', ''), '/ SRU', '') + ', ' +
State AS OptionText, @ViewOrder, @OptionTextboxMode
FROM franchisees
WHERE (Ctry = 'US')
ORDER BY State

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources