Problem with setting variable values in a loop

In a stored procedure that I'm fixing, there is a problem with assigning variable values inside a loop. The proc is using dynamic SQL and if statements to build all these statements, but I'm having to add a new variable value to it that is throwing it out of whack.

This is the current structure:

SET @.MktNbr = 10

WHILE @.MktNbr < 90

BEGIN

DECLARE @.sqlstmt varchar(1000)

SET @.Market = '0' + CONVERT(char(2),@.MktNbr)

SET @.sqlstmt = ' SELECT (columns)
INTO dbo.table' + @.Market + '
FROM #table
WHERE marketcode = ''' + @.Market + '''
IF @.MktNbr = 50
BEGIN
SET @.MktNbr = 51
END
ELSE
IF @.MktNbr = 51
BEGIN
SET @.MktNbr = 52
END
ELSE
IF @.MktNbr = 52
BEGIN
SET @.MktNbr = 55
END
ELSE
IF @.MktNbr = 55
BEGIN
SET @.MktNbr = 60
END
ELSE
BEGIN
SET @.MktNbr = @.MktNbr + 10
END
EXEC (@.sqlstmt)

END

I'm probably having a blonde moment, but I'm trying to replace the if statements with this:

SET @.MktNbr =
CASE
WHEN @.MktNbr = 10 THEN 20
WHEN @.MktNbr = 20 THEN 30
WHEN @.MktNbr = 30 THEN 40
WHEN @.MktNbr = 40 THEN 50
WHEN @.MktNbr = 50 THEN 51
WHEN @.MktNbr = 51 THEN 52
WHEN @.MktNbr = 52 THEN 55
WHEN @.MktNbr = 55 THEN 60
WHEN @.MktNbr = 60 THEN 70
WHEN @.MktNbr = 70 THEN 80
WHEN @.MktNbr = 80 THEN 81
ELSE @.MktNbr END

Clearly it's wrong because the proc bombs every time with a duplicate table error.

It has been suggested to me that I should hold these market values in an external table. This sounds reasonable but I'm ashamed to admit that I don't know how I'd implement that. Can someone maybe give me a nudge in the right direction?That works fine for me:

DECLARE @.MktNbr int

SET @.MktNbr = 30

SET @.MktNbr =
CASE
WHEN @.MktNbr = 10 THEN 20
WHEN @.MktNbr = 20 THEN 30
WHEN @.MktNbr = 30 THEN 40
WHEN @.MktNbr = 40 THEN 50
WHEN @.MktNbr = 50 THEN 51
WHEN @.MktNbr = 51 THEN 52
WHEN @.MktNbr = 52 THEN 55
WHEN @.MktNbr = 55 THEN 60
WHEN @.MktNbr = 60 THEN 70
WHEN @.MktNbr = 70 THEN 80
WHEN @.MktNbr = 80 THEN 81
ELSE @.MktNbr END

PRINT @.MktNbr|||First...dynamic sql...ugh

Second, why are you setting @.market BEFORE you set @.mrktnmbr?

third, non logged creation of a table will fail the second time you need to do the insert

Can you explain, in business terms, what you are trying to accomplish, or what's been asked of you?|||Clearly it's wrong because the proc bombs every time with a duplicate table error.

Clearly

You can only execute it once per table creation.

Also, again, the assignmnet is out of whack

You will always be trying to create the same table, over and over, because the tablename is not being included in your "logic"|||Clearly it's wrong because the proc bombs every time with a duplicate table error.
My guess is that it fails on dbo.table081, right?

When @.MktNbr reaches 81, your case statement assigns it the new value of 81. The loop will try to make table081 again and fails.
You should set it to 90, so the loop will end.|||The answer is:
@.MktNbr never exceeds 81.|||the biggest wtf here is why are there so many market tables? why not just one?|||But as Brett (and now Rudy... Man I'm slow resonding to this thread) as highlighted above - the code is not good!
Even if you have a fix this is not the way for you to be doing this - explain what you're trying to achieve and hopefully we can prod you towards a better solution :)|||First...dynamic sql...ugh

Second, why are you setting @.market BEFORE you set @.mrktnmbr?

third, non logged creation of a table will fail the second time you need to do the insert

Can you explain, in business terms, what you are trying to accomplish, or what's been asked of you?

Fair points...allow me to address them in turn.

First: yes, dynamic SQL can be yucky but this is not something I developed, I am only making a modification to it. ;)

Second: See first point...I didn't write that, somebody else did. Somebody who no longer works here. :angel:

Third: I've had some ideas of things I'm going to try there so I'll get back to you on that. :)|||Second: See first point...I didn't write that, somebody else did. Somebody who no longer works here. :angel:

There's a reason for that|||Let me ask, do the tables get dropped before you hit this code?

How much data are we talking about?

Why not just hard code the 10 statements and not use dynamic sql?

Or, why not use 1 table and add a column for market code?

Really, all of this makes very little sense

So where did the person go? Burger King?|||There's a reason for that

Yep, there is. Thing is, the bossman doesn't want me to re-write the proc since it works...it's just slow. Right now the priority is just to make that amendment.

If you think that's good, there's some other ones that'd probably turn your hair white.|||Let me ask, do the tables get dropped before you hit this code?

How much data are we talking about?

Why not just hard code the 10 statements and not use dynamic sql?

Or, why not use 1 table and add a column for market code?

Really, all of this makes very little sense

So where did the person go? Burger King?

Don't fret, the person who suggested that I needed to set the variable to 90 was right; it works now. :cool:

Some of the procs do have hard-coded statements. Some of the developers here prefer the dynamic sql because they feel it's easier to maintain. I'm new here so I'm not in a position to tell them their code sucks, particularly since I'm the least experienced of the group. And yes, the tables get dropped; that's the first thing that happens in the proc. Right now we're not doing any design changes.|||that'd probably turn your hair white.

too late, the margarita's took care of that

And btw, what's "Too slow"

Instead of moving the data, why not just create views that are the name of the tables you are creating?

Oh, and if the smucks think your a jr. dba/developer, just keep coming here.

We'll smoke'em|||too late, the margarita's took care of that

And btw, what's "Too slow"

Instead of moving the data, why not just create views that are the name of the tables you are creating?

Oh, and if the smucks think your a jr. dba/developer, just keep coming here.

We'll smoke'em

This particular proc takes over an hour to execute.

Right now I'm testing one that has been executing for over four hours. It's obscene. :Ssql

Problem with SELECT

I constructed the following SQL statement by studying the example on
page 392 in Itzik Ben Gan et al.'s "Inside Microsoft SQL Server 2005:
T-SQL Querying"

SELECT s_supplier_code, s_supplier_name FROM suppliers AS S1
WHERE s_supplier_code =
(
SELECT TOP(1) * FROM suppliers AS S2
WHERE S2.s_supplier_code = S1.s_supplier_code
ORDER BY s_supplier_name
)
;

However, it generates the following error.

Msg 116, Level 16, State 1, Line 4
Only one expression can be specified in the select list when the
subquery is not introduced with EXISTS.

This isn't a showstopper since the following statement does much the
same thing and it works (even though it is a little less flexible).

SELECT s_supplier_code,MAX(s_supplier_name) FROM suppliers GROUP BY
s_supplier_code;

My code can continue on, but I want to understand why the statement I
constructed by following the example in my book was rejected. Did I
miss something? Or is there an error in the book? Or is there a bug
in SQL Server 2005?

Thanks

Ted>My code can continue on, but I want to understand why the statement I constructed by following the example in my book was rejected. Did I miss something? Or is there an error in the book? Or is there a bug in SQL Server 2005? <<

This is just how God punishes people for using proprietary code :)
Since this is MS-only
syntax, they can do anything they wish from release to release. Ergo,
you are screwed and live at their whim without recourse to a
higherauthority.

Drop an email to the publisher, so they can fix it in the book and use
the Standard. MAX() syntax you provided. Since it is Standard SQL, MS
has to keep it the same from release to release.|||--CELKO-- wrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

My code can continue on, but I want to understand why the statement I constructed by following the example in my book was rejected. Did I miss something? Or is there an error in the book? Or is there a bug in SQL Server 2005? <<

>
This is just how God punishes people for using proprietary code :)
Since this is MS-only
syntax, they can do anything they wish from release to release. Ergo,
you are screwed and live at their whim without recourse to a
higherauthority.
>
Drop an email to the publisher, so they can fix it in the book and use
the Standard. MAX() syntax you provided. Since it is Standard SQL, MS
has to keep it the same from release to release.

I think I feel a headache coming on!

The publisher is Microsoft Press. It is for SQL Server 2005 and I AM
RUNNING SQL Server 2005! You'd think they'd run sample code from their
own book against their own SQL Server.

Cheers,

Ted

Someone pass me the super strength Motrin! :-(|||* Ted wrote, On 21.08.2006 17:28:

Quote:

Originally Posted by

I constructed the following SQL statement by studying the example on
page 392 in Itzik Ben Gan et al.'s "Inside Microsoft SQL Server 2005:
T-SQL Querying"
>
SELECT s_supplier_code, s_supplier_name FROM suppliers AS S1
WHERE s_supplier_code =
(
SELECT TOP(1) * FROM suppliers AS S2
WHERE S2.s_supplier_code = S1.s_supplier_code
ORDER BY s_supplier_name
)
;
>
However, it generates the following error.
>
Msg 116, Level 16, State 1, Line 4
Only one expression can be specified in the select list when the
subquery is not introduced with EXISTS.

What happens if you replace
select top(1) *
with this?
select top(1) some_column_name

Boa
[snip]|||Ted wrote:

Quote:

Originally Posted by

I constructed the following SQL statement by studying the example on
page 392 in Itzik Ben Gan et al.'s "Inside Microsoft SQL Server 2005:
T-SQL Querying"
>
SELECT s_supplier_code, s_supplier_name FROM suppliers AS S1
WHERE s_supplier_code =
(
SELECT TOP(1) * FROM suppliers AS S2
WHERE S2.s_supplier_code = S1.s_supplier_code
ORDER BY s_supplier_name
)
;
>

Ted,

The example in Listing 7-1 is different from your query, and the
example works (and it makes perfect sense to me). I am not sure I
understand what you were trying to accomplish. Note that:
1. Listing 7-1 uses SELECT TOP(1) OrderId, as opposed to your top(1) *
2. Listing 7-1 uses different columns to filter the result set
(OrderId) and to correlate the subquery (EmployeeId). Using one and the
same solumn s_supplier_code in both cases makes little sense to me.

Good luck!|||boa wrote:

Quote:

Originally Posted by

* Ted wrote, On 21.08.2006 17:28:

Quote:

Originally Posted by

I constructed the following SQL statement by studying the example on
page 392 in Itzik Ben Gan et al.'s "Inside Microsoft SQL Server 2005:
T-SQL Querying"

SELECT s_supplier_code, s_supplier_name FROM suppliers AS S1
WHERE s_supplier_code =
(
SELECT TOP(1) * FROM suppliers AS S2
WHERE S2.s_supplier_code = S1.s_supplier_code
ORDER BY s_supplier_name
)
;

However, it generates the following error.

Msg 116, Level 16, State 1, Line 4
Only one expression can be specified in the select list when the
subquery is not introduced with EXISTS.

>
>
What happens if you replace
select top(1) *
with this?
select top(1) some_column_name
>

Nothing changes. The same error is generated.

Thanks

Ted|||Since this is MS-only

Quote:

Originally Posted by

syntax, they can do anything they wish from release to release. Ergo,
you are screwed and live at their whim without recourse to a
higherauthority.
>
Drop an email to the publisher, so they can fix it in the book and use
the Standard. MAX() syntax you provided. Since it is Standard SQL, MS
has to keep it the same from release to release.

Joe,

Oracle and DB2 also provide proprietary features for this purpose, and
those features are also widely used. Looks like something is missing in
the standard, right?|||You have incorrectly reproduced the query from Itziks book (just checked
myself)

Check back again, you will see that you have put * (all columns) you should
have used a single column key in Itziks example it was OrderId.

SELECT s_supplier_code, s_supplier_name
FROM suppliers AS S1
WHERE s_supplier_code =
(
SELECT TOP(1) OrderId FROM suppliers AS S2
WHERE S2.s_supplier_code = S1.s_supplier_code
ORDER BY s_supplier_name
)

Tony.

--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials

"Ted" <r.ted.byers@.rogers.comwrote in message
news:1156177703.807874.29310@.b28g2000cwb.googlegro ups.com...

Quote:

Originally Posted by

>I constructed the following SQL statement by studying the example on
page 392 in Itzik Ben Gan et al.'s "Inside Microsoft SQL Server 2005:
T-SQL Querying"
SELECT s_supplier_code, s_supplier_name FROM suppliers AS S1
WHERE s_supplier_code =
(
SELECT TOP(1) * FROM suppliers AS S2
WHERE S2.s_supplier_code = S1.s_supplier_code
ORDER BY s_supplier_name
)
;
However, it generates the following error.
Msg 116, Level 16, State 1, Line 4
Only one expression can be specified in the select list when the
subquery is not introduced with EXISTS.
This isn't a showstopper since the following statement does much the
same thing and it works (even though it is a little less flexible).
SELECT s_supplier_code,MAX(s_supplier_name) FROM suppliers GROUP BY
s_supplier_code;
My code can continue on, but I want to understand why the statement I
constructed by following the example in my book was rejected. Did I
miss something? Or is there an error in the book? Or is there a bug
in SQL Server 2005?
Thanks
Ted
>

|||This is just how God punishes people for using proprietary code :)

Quote:

Originally Posted by

Since this is MS-only
syntax, they can do anything they wish from release to release. Ergo,
you are screwed and live at their whim without recourse to a
higherauthority.
>

As ever you are talking utter rubbish.

It takes 2 or 3 or sometimes more releases to deprecate and then discontinue
a feature, actually - thats better than the standard SQL cycle where you
only get 4 year was it celko? And you don't get any warning there either
unless you subscribe and pay the fees....

Quote:

Originally Posted by

Drop an email to the publisher, so they can fix it in the book and use
the Standard. MAX() syntax you provided. Since it is Standard SQL, MS
has to keep it the same from release to release.
>

So, just how would you using standard SQL do the equivelant of this without
using a 'scratch tape' or relying on the application to dig you out of the
featureless ansi sql standard?

-- Get last 3 most recent sales...
select ...
from sales
where orderid in (
select top 3 orderid
from sales
order by orderdate desc
)

--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials

"--CELKO--" <jcelko212@.earthlink.netwrote in message
news:1156180035.849964.264800@.b28g2000cwb.googlegr oups.com...

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

>>My code can continue on, but I want to understand why the statement I
>>constructed by following the example in my book was rejected. Did I
>>miss something? Or is there an error in the book? Or is there a bug in
>>SQL Server 2005? <<

>
This is just how God punishes people for using proprietary code :)
Since this is MS-only
syntax, they can do anything they wish from release to release. Ergo,
you are screwed and live at their whim without recourse to a
higherauthority.
>
Drop an email to the publisher, so they can fix it in the book and use
the Standard. MAX() syntax you provided. Since it is Standard SQL, MS
has to keep it the same from release to release.
>

|||Thanks Tony and Alexander

Thanks to your posts, I now understand what I missed and I now have it
working.

Thanks

Ted

Problem with repeater

I'm new to ASP.NET and i've been banging my head aganist this problem for a few days now.

I have a repeater and then a repeater inside that repeater.

<asp:SqlDataSource ID="sqlRepeater1" runat="server" ConnectionString="<%$ ConnectionStrings:Inspections_DBCS %>"
SelectCommand="SELECT count(*) as mycount FROM insuranceco with (NOLOCK) where insurancecoid=@.insurancecoid" OnSelecting="sqlRepeater_Selecting" >
<SelectParameters>
<asp:Parameter DefaultValue='' Name="insurancecoid" Type="String" />
</SelectParameters>
</asp:SqlDataSource
<%
'sqlRepeater1.SelectParameters["insurancecoid"].DefaultValue = "123";
%
This returns:
Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.
Compiler Error Message: BC30451: Name 'sqlRepeater1' is not declared.
Source Error:

Line 38:
Line 39: <%
Line 40: sqlRepeater1.SelectParameters["insurancecoid"].DefaultValue = "123";
Line 41: %>
Line 42:

I tried a solution i saw on the web about using the onselecting event but that didn't pan out either

Protected Sub sqlRepeater_Selecting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs) 'Handles sqlRepeater1.Selecting WithEvents
'Response.Write(e.Command.Parameters.Add)
'e.Command.Parameters.Add(e.Command.CreateParameter("P1", "test"))
'e.Command.Parameters.Add("P1", "value")

I have to be doing something wrong because i would think there has to be a way to reference variables when you are going from repeater to repeater i just dont know.

Please help my mask of sanity is starting to slip off.

Thanks

You can put this line in any event in the code behind file:

sqlRepeater1.SelectParameters["insurancecoid"].DefaultValue = "123";

You're using SqlDataSource yet I haven't seen where is the repeater.

|||There is the whole page as it is. I'm going to try putting that
sqlRepeater1.SelectParameters["insurancecoid"].DefaultValue = "123";

in the codebehind now and see what happens.

<%@. Page Language="VB" MasterPageFile="~/MasterPage.master" AutoEventWireup="false" CodeFile="ProcessOrders.aspx.vb" Inherits="ProcessOrders" title="Process Inspection Orders" %
<asp:Content ID="Content1" ContentPlaceHolderID="CPH1" Runat="Server"
<h1>Process Inspection Orders</h1>
<hr /
<asp:SqlDataSource ID="InspectionCompanys" runat="server" ConnectionString="<%$ ConnectionStrings:Inspections_DBCS %>"
SelectCommand="select inspectioncompany,inspectioncoid from inspectionco with (NOLOCK)"
</asp:SqlDataSource
<asp:SqlDataSource ID="sqlRepeater" runat="server" ConnectionString="<%$ ConnectionStrings:Inspections_DBCS %>"
SelectCommand="SELECT insurancecompany,insurancecoid FROM insuranceco with (NOLOCK) ">
</asp:SqlDataSource
<asp:Repeater id="ProcessOrder" EnableViewState="False" runat="server" DataSourceID="sqlRepeater">
<HeaderTemplate>
<table width="600" cellpadding="3" cellspacing="0">
</HeaderTemplate>
<ItemTemplate
<asp:SqlDataSource ID="sqlRepeater1" runat="server" ConnectionString="<%$ ConnectionStrings:Inspections_DBCS %>"
SelectCommand="SELECT count(*) as mycount FROM insuranceco with (NOLOCK) where insurancecoid=@.insurancecoid" OnSelecting="sqlRepeater_Selecting" >
<SelectParameters>
<asp:Parameter DefaultValue='' Name="insurancecoid" Type="String" />
</SelectParameters>
</asp:SqlDataSource
<%
sqlRepeater1.SelectParameters["insurancecoid"].DefaultValue = "123";
%
<tr>
<td style='width: 35%; height: 32px'>
<asp:Label ID="lblViewCompanyName" runat="server" Text='<%# EVAL("InsuranceCompany") %>'/></td>
<td style='height: 32px'>
<asp:Repeater ID="InsideLoop" EnableViewState=false runat="server" DataSourceID="SqlRepeater1">
<ItemTemplate>
<asp:Label ID="numberofrecords" runat="server" Text='<%# EvaL("mycount") %>'/>
</ItemTemplate>
</asp:Repeater
</td>
<td style='height: 32px'>
<asp:Label ID="lblInspectionCompany" runat="server" Text="Inspection Company:"/></td>
<td style='height: 32px'>
<asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="InspectionCompanys" DataTextField="inspectioncompany" DataValueField="inspectioncoid" Value="test" />
</td>
<td>
<asp:Label runat="server" ID="lblAssignDate" Text="Assign Date:"></asp:Label><asp:TextBox ID="txtAssignDate1" runat="server"/>
</td>
<td>
<asp:Button ID="btAssign" runat="server" Text="Assign" />
</td>
</tr>
</ItemTemplate>
<SeparatorTemplate>
<!--<hr />-->
</SeparatorTemplate>
<FooterTemplate>
</table>
</FooterTemplate>
</asp:Repeater>
</asp:Content|||Here is the codebehind

Imports System.Data
Imports System.Data.SqlClient

Public Class ProcessOrders
Inherits Page

Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

'Call BuildUserGrid()

End Sub

Sub BuildUserGrid(ByRef txtAssignDate As TextBox)

'ProcessOrder.DataSource = GetOrder()
'ProcessOrder.DataBind()
'Call GetOrder()
txtAssignDate.Text = "TEST"

End Sub

Function GetOrder() As DataView

Dim dstUsers As DataSet
Dim objConnection As SqlConnection
Dim dadResults As SqlDataAdapter
Dim dvwUsers As DataView

Dim strServer As String 'Which server is this connection
Dim strDatabase As String 'Which database is this connection
Dim strUserID As String 'What SQL Server UserName
Dim strPassword As String 'What SQL Server Password
Dim strQuery As String 'My SQL QUERY

strServer = ConfigurationSettings.AppSettings("server")
strDatabase = ConfigurationSettings.AppSettings("database")
strUserID = ConfigurationSettings.AppSettings("userid")
strPassword = ConfigurationSettings.AppSettings("password")

strQuery = "SELECT insurancecompany "
strQuery = strQuery & "FROM insuranceco with (NOLOCK) "

Response.Write("WOW")

'dvwUsers = Cache("MyInspection")
If dvwUsers Is Nothing Then
dstUsers = New DataSet()
objConnection = New SqlConnection("Server=" & strServer & ";UID=" & strUserID & ";PWD=" & strPassword & ";Database=" & strDatabase)
dadResults = New SqlDataAdapter(strQuery, objConnection)
dadResults.Fill(dstUsers, "MyInspection")
dvwUsers = dstUsers.Tables("MyInspection").DefaultView()
'Cache("MyInspection") = dvwUsers
End If

'objConnection.Close()

Return dvwUsers

End Function

Protected Sub sqlRepeater_Selecting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs) 'Handles sqlRepeater1.Selecting WithEvents
'Response.Write(e.Command.Parameters.Add)
'e.Command.Parameters.Add(e.Command.CreateParameter("P1", "test"))
'e.Command.Parameters.Add("P1", "value")

'insurancecoid

End Sub
End Class|||When i put the sqlrepeater1 line it gives me an error.
But it only errors when i have the repeater inside the first repeater.
Is it impossible to do a repeater inside a repeater? Is there some longer way to reference a variable when you are inside a repeater because it just seems like it is something to do with being inside repeater #1.

If i put the 2nd repeater (sqlrepeater1) and the code outside of the first repeater it works.

Compiler Error Message: BC30451: Name 'sqlRepeater1' is not declared.

Source Error:

Line 73: 'insurancecoid
Line 74:
Line 75: sqlRepeater1.SelectParameters["insurancecoid"].DefaultValue = "123";
Line 76: End Sub
Line 77: End Class