Dynamic SQL!

As much as i would prefer not to..it appears the only way i can acheive
something is to use dynamic sql.
I have constructed the test query below, but only recieve invalid column
name errors.
Can someone tell me where i am going wrong?
I am obvsiouly missing something quite simple'
DECLARE @.sqlquery VARCHAR(400)
SET @.sqlquery = "SELECT asmt_v2_question_id FROM asmt_v2_questions"
PRINT @.sqlqueryAdam,
Why do you believe Dynamic SQL is the only way?
Try :
DECLARE @.sqlquery VARCHAR(400)
SET @.sqlquery = 'SELECT asmt_v2_question_id FROM asmt_v2_questions'
EXEC (@.sqlquery)
HTH
Jerry
"Adam Knight" <adam@.pertrain.com.au> wrote in message
news:e2RGkH7xFHA.3892@.TK2MSFTNGP12.phx.gbl...
> As much as i would prefer not to..it appears the only way i can acheive
> something is to use dynamic sql.
> I have constructed the test query below, but only recieve invalid column
> name errors.
> Can someone tell me where i am going wrong?
> I am obvsiouly missing something quite simple'
> DECLARE @.sqlquery VARCHAR(400)
> SET @.sqlquery = "SELECT asmt_v2_question_id FROM asmt_v2_questions"
> PRINT @.sqlquery
>|||Use single quotes to enclose your SQL statement literal like the example
below. Double-quotes should be used only to enclose identifiers when SET
QUOTED_IDENTIFIER is on. The 'invalid column' error results because SQL
Server thinks you are specifying a column name.
SET @.sqlquery = 'SELECT asmt_v2_question_id FROM asmt_v2_questions'
Hope this helps.
Dan Guzman
SQL Server MVP
"Adam Knight" <adam@.pertrain.com.au> wrote in message
news:e2RGkH7xFHA.3892@.TK2MSFTNGP12.phx.gbl...
> As much as i would prefer not to..it appears the only way i can acheive
> something is to use dynamic sql.
> I have constructed the test query below, but only recieve invalid column
> name errors.
> Can someone tell me where i am going wrong?
> I am obvsiouly missing something quite simple'
> DECLARE @.sqlquery VARCHAR(400)
> SET @.sqlquery = "SELECT asmt_v2_question_id FROM asmt_v2_questions"
> PRINT @.sqlquery
>|||Also,
If you haven't already read it, you might want to take a look at Erland's
article:
The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
HTH
Jerry
"Adam Knight" <adam@.pertrain.com.au> wrote in message
news:e2RGkH7xFHA.3892@.TK2MSFTNGP12.phx.gbl...
> As much as i would prefer not to..it appears the only way i can acheive
> something is to use dynamic sql.
> I have constructed the test query below, but only recieve invalid column
> name errors.
> Can someone tell me where i am going wrong?
> I am obvsiouly missing something quite simple'
> DECLARE @.sqlquery VARCHAR(400)
> SET @.sqlquery = "SELECT asmt_v2_question_id FROM asmt_v2_questions"
> PRINT @.sqlquery
>|||Hi Jerry,
The query below is the one in question.
I am still learning the functions and logic structures of TSQL .
Consequently, so far i can't think of anyway of doing the following without
Dynamic Sql (which i would prefer not to).
A previous post also dealt with this issue (been trying to nut this query
out for a while).
I received some responses, though quite helpful and very much appreciated, i
am not sure they really dealt with my particular problem.
(This would be my fault for providing an overly simplified query).
The issue is parameterizing the "TOP" statement .
Or somehow limiting dynamically (via parameter, variable) the number of
rows returned by the SECOND (Only) query in the UNION.
-- select question details where questions are compulsory, within a given
area
-- and not answered in a previous completed assessment attempt
SELECT
NewID(),asmt_v2_question_id, qtext, qindex, qtype, answer_url
FROM
asmt_v2_questions
WHERE qtype = 'c' AND asmt_v2_area_id = @.aid AND asmt_v2_question_id
NOT IN (
-- retreive question ids (compulsory) in 'asmt_question_results' table
-- that are associated with a given set of attempt_ids
SELECT DISTINCT
asmt_v2_questions.asmt_v2_question_id
FROM
asmt_v2_questions
INNER JOIN
asmt_v2_question_results
ON
asmt_v2_questions.asmt_v2_question_id =
asmt_v2_question_results.asmt_v2_question_id
WHERE
asmt_v2_questions.qtype = 'c'
AND
asmt_v2_question_results.asmt_v2_attempt_id
IN (
-- retreive attempt_ids that are associated with completed assessments
SELECT
asmt_v2_completed.asmt_v2_attempt_id
FROM
asmt_v2_completed
INNER JOIN
asmt_v2_attempts
ON
asmt_v2_completed.asmt_v2_attempt_id =
asmt_v2_attempts.asmt_v2_attempt_id
WHERE
asmt_v2_attempts.asmt_v2_employee_id = @.eid AND
asmt_v2_attempts.asmt_v2_area_id = @.aid
)
)
UNION ALL
-- select question details from results returned in sub query
SELECT
NEWID() AS ID, asmt_v2_question_id, qtext, qindex, qtype, answer_url
FROM (
-- select question details where questions are optional, within a given area
-- and not answered in a previous completed assessment attempt
-- only return a specified number of rows
SELECT
TOP 2 NEWID() AS ID,asmt_v2_question_id, qtext, qindex, qtype,
answer_url /* TOP STATEMENT TO BE PARAMETERISE */
FROM
asmt_v2_questions
WHERE
qtype = 'o'
AND
asmt_v2_area_id = @.aid
AND
asmt_v2_question_id
NOT IN (
-- retreive question ids (optional questions) in 'asmt_question_results'
table
-- that are associated with a given set of attempt_ids
SELECT DISTINCT
asmt_v2_questions.asmt_v2_question_id
FROM
asmt_v2_questions
INNER JOIN
asmt_v2_question_results
ON
asmt_v2_questions.asmt_v2_question_id =
asmt_v2_question_results.asmt_v2_question_id
WHERE
asmt_v2_questions.qtype = 'o'
AND
asmt_v2_question_results.asmt_v2_attempt_id
IN (
-- retreive attempt_ids that are associated with completed assessments
SELECT
asmt_v2_completed.asmt_v2_attempt_id
FROM
asmt_v2_completed
INNER JOIN
asmt_v2_attempts
ON
asmt_v2_completed.asmt_v2_attempt_id =
asmt_v2_attempts.asmt_v2_attempt_id
WHERE
asmt_v2_attempts.asmt_v2_employee_id = @.eid
AND
asmt_v2_attempts.asmt_v2_area_id = @.aid
)
)
ORDER BY 1|||Try SET ROWCOUNT in BOL.
"Adam Knight" <adam@.pertrain.com.au> wrote in message
news:eEmek27xFHA.2556@.TK2MSFTNGP10.phx.gbl...
> Hi Jerry,
> The query below is the one in question.
> I am still learning the functions and logic structures of TSQL .
> Consequently, so far i can't think of anyway of doing the following
> without Dynamic Sql (which i would prefer not to).
> A previous post also dealt with this issue (been trying to nut this query
> out for a while).
> I received some responses, though quite helpful and very much appreciated,
> i am not sure they really dealt with my particular problem.
> (This would be my fault for providing an overly simplified query).
> The issue is parameterizing the "TOP" statement .
> Or somehow limiting dynamically (via parameter, variable) the number of
> rows returned by the SECOND (Only) query in the UNION.
>
> -- select question details where questions are compulsory, within a given
> area
> -- and not answered in a previous completed assessment attempt
> SELECT
> NewID(),asmt_v2_question_id, qtext, qindex, qtype, answer_url
> FROM
> asmt_v2_questions
> WHERE qtype = 'c' AND asmt_v2_area_id = @.aid AND asmt_v2_question_id
> NOT IN (
> -- retreive question ids (compulsory) in 'asmt_question_results' table
> -- that are associated with a given set of attempt_ids
> SELECT DISTINCT
> asmt_v2_questions.asmt_v2_question_id
> FROM
> asmt_v2_questions
> INNER JOIN
> asmt_v2_question_results
> ON
> asmt_v2_questions.asmt_v2_question_id =
> asmt_v2_question_results.asmt_v2_question_id
> WHERE
> asmt_v2_questions.qtype = 'c'
> AND
> asmt_v2_question_results.asmt_v2_attempt_id
> IN (
> -- retreive attempt_ids that are associated with completed assessments
> SELECT
> asmt_v2_completed.asmt_v2_attempt_id
> FROM
> asmt_v2_completed
> INNER JOIN
> asmt_v2_attempts
> ON
> asmt_v2_completed.asmt_v2_attempt_id =
> asmt_v2_attempts.asmt_v2_attempt_id
> WHERE
> asmt_v2_attempts.asmt_v2_employee_id = @.eid AND
> asmt_v2_attempts.asmt_v2_area_id = @.aid
> )
> )
> UNION ALL
> -- select question details from results returned in sub query
> SELECT
> NEWID() AS ID, asmt_v2_question_id, qtext, qindex, qtype, answer_url
> FROM (
> -- select question details where questions are optional, within a given
> area
> -- and not answered in a previous completed assessment attempt
> -- only return a specified number of rows
> SELECT
> TOP 2 NEWID() AS ID,asmt_v2_question_id, qtext, qindex, qtype,
> answer_url /* TOP STATEMENT TO BE PARAMETERISE */
> FROM
> asmt_v2_questions
> WHERE
> qtype = 'o'
> AND
> asmt_v2_area_id = @.aid
> AND
> asmt_v2_question_id
> NOT IN (
> -- retreive question ids (optional questions) in
> 'asmt_question_results' table
> -- that are associated with a given set of attempt_ids
> SELECT DISTINCT
> asmt_v2_questions.asmt_v2_question_id
> FROM
> asmt_v2_questions
> INNER JOIN
> asmt_v2_question_results
> ON
> asmt_v2_questions.asmt_v2_question_id =
> asmt_v2_question_results.asmt_v2_question_id
> WHERE
> asmt_v2_questions.qtype = 'o'
> AND
> asmt_v2_question_results.asmt_v2_attempt_id
> IN (
> -- retreive attempt_ids that are associated with completed assessments
> SELECT
> asmt_v2_completed.asmt_v2_attempt_id
> FROM
> asmt_v2_completed
> INNER JOIN
> asmt_v2_attempts
> ON
> asmt_v2_completed.asmt_v2_attempt_id =
> asmt_v2_attempts.asmt_v2_attempt_id
> WHERE
> asmt_v2_attempts.asmt_v2_employee_id = @.eid
> AND
> asmt_v2_attempts.asmt_v2_area_id = @.aid
> )
> )
> ORDER BY 1
>|||Umm..as mentioned in my post i only want to limit the number of rows
returned in the SECOND query of the UNION.
Unless i am MISSING something, SET ROWCOUNT witll apply to all results
returned in the query!
Adam
"Michael C#" <xyz@.abcdef.com> wrote in message
news:Vg10f.2316$Hd2.2048@.fe11.lga...
> Try SET ROWCOUNT in BOL.
> "Adam Knight" <adam@.pertrain.com.au> wrote in message
> news:eEmek27xFHA.2556@.TK2MSFTNGP10.phx.gbl...
>|||Adam Knight wrote:
> Umm..as mentioned in my post i only want to limit the number of rows
> returned in the SECOND query of the UNION.
> Unless i am MISSING something, SET ROWCOUNT witll apply to all results
> returned in the query!
>
You can store the smaller result set (probably the one with the TOP in a
temp table) and then union the temp table with the other query and avoid
dynamic sql altogether.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||THANK YOU!!!!!
I think we may have a useable option.
One last question to nail this.
I am trying to create the temp table and insert the relevant data.
I am recieving this error:
Insert Error: Column name or number of supplied values does not match table
definition.
Any thoughts?
As far as i can tell the column definitions are the same..
CREATE TABLE #tempTable (
[asmt_v2_question_id] [int] NOT NULL,
[qtext] [varchar](255) NULL,
[qindex] [tinyint] NULL,
[qtype] [char](1) NULL,
[answer_url] [varchar](255) NULL
)
INSERT INTO #tempTable
SELECT
asmt_v2_question_id, qtext, qindex, qtype, answer_url
FROM
asmt_v2_questions
WHERE
qtype = 'o'
AND
asmt_v2_area_id = @.aid
AND
asmt_v2_question_id
NOT IN (
SELECT DISTINCT
asmt_v2_questions.asmt_v2_question_id
FROM
asmt_v2_questions
INNER JOIN
asmt_v2_question_results
ON
asmt_v2_questions.asmt_v2_question_id =
asmt_v2_question_results.asmt_v2_question_id
WHERE
asmt_v2_questions.qtype = 'o'
AND
asmt_v2_question_results.asmt_v2_attempt_id
IN (
SELECT
asmt_v2_completed.asmt_v2_attempt_id
FROM
asmt_v2_completed
INNER JOIN
asmt_v2_attempts
ON
asmt_v2_completed.asmt_v2_attempt_id =
asmt_v2_attempts.asmt_v2_attempt_id
WHERE
asmt_v2_attempts.asmt_v2_employee_id = @.eid
AND
asmt_v2_attempts.asmt_v2_area_id = @.aid
)
)
ORDER BY NEWID()
SELECT * FROM #tempTable
CREATE TABLE [dbo].[asmt_v2_questions](
[asmt_v2_question_id] [int] IDENTITY(1,1) NOT NULL,
[asmt_v2_area_id] [int] NULL,
[qtext] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[qindex] [tinyint] NULL,
[qtype] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[answer_url] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_assmnt_questions] PRIMARY KEY CLUSTERED
(
[asmt_v2_question_id] ASC
) ON [PRIMARY]
) ON [PRIMARY]|||Inside the INSERT...SELECT statement you must explicitly state which columns
you're inserting into.
INSERT INTO #tempTable
(
asmt_v2_question_id
,qtext
,qindex
,qtype
,answer_url
)
SELECT
asmt_v2_question_id, qtext, qindex, qtype, answer_url
FROM
asmt_v2_questions
<snip>
ML