hi their

Hi

just i need to know how are we concatenate select statement if we have more if condition in side the strode procedure.

as a example

ssql = 'select * from pat'

and i have more selection criteria

whet i need to do is
if (@.pct is not null)
begin
ssql = ssql +' , '+ 'where pct_cd = @.pct
end

any idea

thank you
Niranga

Code Snippet

declare @.sql nvarchar(4000),

@.pct numeric(5,4)

set @.sql =N'SELECT fld1, fld2 FROM table'

if @.pct isnotnull

begin

set @.sql = @.sql +' where pct_cd = '+cast(@.pct asnvarchar(20))

end

execsp_executesql @.sql

|||thank you very much daleJ|||

No problem Niranga.

If that is a satisfactory solution, please mark it as an answer.

|||HI

It's working properly and when I'm executing it says Command(s) completed successfully. but now the problem is not gain any output is there a specific way to execute it.|||

Nope.

Just execute sp_executesql.

Make sure your query returns rows when executed without using the stored procedure to invoke it.

sql

HELPPP SQL select statement pissing me off, error, but why.....

grr.
I have two different select statements that are giving me the same error.. and its getting irritating cause i cant figure out why...

the error IS --tada--

-------------
ADODB.Recordset error '800a0bb9'

Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

/vendorlist.asp, line 68
-------------
Here's the statement.. and ill point out line 68 below with a few other details...
-------------
Dim vendorRS, SQL
set vendorRS = Server.CreateObject("ADODB.Recordset")
SQL = "SELECT ttVendors.company, ttVendors.website, ttVendors.phone, ttVendors.phone800,ttVendors.vendorID," &_
SQL = SQL & "ttInventory.newprice, ttInventory.refurbprice, ttInventory.oosprice, ttInventory.venddescrip" &_
SQL = SQL & "FROM ttVendors INNER JOIN ttInventory on ttVendors.vendorID = ttInventory.vendorID" &_
SQL = SQL & "WHERE ttInventory.prodID = '" &prodID& "'"

vendorRS.Open SQL ,objCn, 1, 3

DIM company
company = vendorRs("company")
vendorRs.close
set vendorRs = nothing
--------------
Ok so... line 68 IS
vendorRS.Open SQL ,objCn, 1, 3

ive tried it like
vendorRS.Open = SQL ,objCn, 1, 3
vendorRS.Open, SQL ,objCn, 1, 3
vendorRS.Open SQL ,objCn, 3, 3

no luck..
Also, prodID is passed from a querystring from previous page.. just used the typical request object to gather.. in the DB, all prodID fields are of INT type.. and im wondering if that makes a difference..

any ideas.. or blanks i can fill it?PS,, in Query analyzer, the statement works perfect...!!

Help: Table name displaying twice in SQL Statement inside SQL Server Enterprise Manager

For some reason whenever I look at the SQL statement of a particular table, the table name displays twice.
For example,
SELECT * FROM State State
Even when I execute this statement, it still returns the correct results. It does this for all tables in this particular database. I also check another database and thoses display the table names in the SQL statements correctly.
Does anyone know why the table name would display twice in a table inside of a particular database?

How (where) are these statements being generated? All this is doing is creating an alias for the table with its own name, so this won't hurt anything. It's much like doing:
SELECT A.Field1 FROM State A
Marcie|||

datagridgirl wrote:

How (where) are these statements being generated? All this is doing is creating an alias for the table with its own name, so this won't hurt anything. It's much like doing:
SELECT A.Field1 FROM State A
Marcie

It just came up whenever I look at the SQL statement (SQL Pane) after "Returning All Rows" of a particular table. I guess it is not a big deal. I just don't know why it would do that.|||

voodoo9055 wrote:

It just came up whenever I look at the SQL statement (SQL Pane) after "Returning All Rows" of a particular table. I guess it is not a big deal. I just don't know why it would do that.

I'm curious now...in what tool? I'm not seeing this behavior in either VS.NET (via Server Explorer) or Enterprise Manager...
Marcie|||

datagridgirl wrote:

voodoo9055 wrote:

It just came up whenever I look at the SQL statement (SQL Pane) after "Returning All Rows" of a particular table. I guess it is not a big deal. I just don't know why it would do that.

I'm curious now...in what tool? I'm not seeing this behavior in either VS.NET (via Server Explorer) or Enterprise Manager...
Marcie

Enterprise Manager.
It displays that way for one of my databases. The rest display normally.

Help: Complex Select Statement

Here is my SQL string:

"SELECT to_ordnum, to_orddate," _
& "(SELECT SUM((DDPROD.pr_stanmat * DDPROD.pr_prfact) *
(DOBOM2.b2_quant * DDORD.or_quant)) FROM DDPROD INNER JOIN DOBOM2 ON
DDPROD.pr_prodnum = DOBOM2.b2_prodnum INNER JOIN DDORD ON
DOBOM2.b2_orid = DDORD.or_id INNER JOIN DDTORD ON DDORD.OR_TOID =
DDTORD.TO_ID WHERE DOBOM2.b2_ordnum = ''order number here from result
of outer select) AS Total" _
& "FROM DDTORD WHERE to_trak2id IN (39, 40, 41) AND to_ordtype = 's'
AND to_status = 'c' GROUP BY to_ordnum, to_orddate ORDER BY to_ordnum
DESC"

The outter Select statement returns various amounts of order numbers
represented by 'to_ordnum' in the outer Select clause which has to
meet the critera in the outer WHERE clause. I would like to place
these numbers selected into the inner WHERE clause for the inner
select statement where DOMBOM2.b2_ordnum = ?the order selected by
outer select statement.

I have tried placing to_ordnum into that location but the SQL2000
server does not process it.

Any suggestions, ideas?

Thank you,

BrettOn 5 Nov 2004 10:34:48 -0800, brett wrote:

> Here is my SQL string:
> "SELECT to_ordnum, to_orddate," _
> & "(SELECT SUM((DDPROD.pr_stanmat * DDPROD.pr_prfact) *
> (DOBOM2.b2_quant * DDORD.or_quant)) FROM DDPROD INNER JOIN DOBOM2 ON
> DDPROD.pr_prodnum = DOBOM2.b2_prodnum INNER JOIN DDORD ON
> DOBOM2.b2_orid = DDORD.or_id INNER JOIN DDTORD ON DDORD.OR_TOID =
> DDTORD.TO_ID WHERE DOBOM2.b2_ordnum = ''order number here from result
> of outer select) AS Total" _
> & "FROM DDTORD WHERE to_trak2id IN (39, 40, 41) AND to_ordtype = 's'
> AND to_status = 'c' GROUP BY to_ordnum, to_orddate ORDER BY to_ordnum
> DESC"
> The outter Select statement returns various amounts of order numbers
> represented by 'to_ordnum' in the outer Select clause which has to
> meet the critera in the outer WHERE clause. I would like to place
> these numbers selected into the inner WHERE clause for the inner
> select statement where DOMBOM2.b2_ordnum = ?the order selected by
> outer select statement.
> I have tried placing to_ordnum into that location but the SQL2000
> server does not process it.
> Any suggestions, ideas?
> Thank you,
> Brett

God, what a mess you posted. If you have a SQL question, try to get the
SQL separated from the client code that is sending it.

The confusion seems to be because you have DDTORD both in the outer select
and the inner select. You should use a table alias to differentiate them.

Here's one possibility, reformatted so it can be read:

SELECT
TBL1.to_ordnum,
TBL1.to_orddate,
( SELECT
SUM(
( DDPROD.pr_stanmat * DDPROD.pr_prfact)
* ( DOBOM2.b2_quant * DDORD.or_quant)
)
FROM DDPROD
INNER JOIN DOBOM2
ON DDPROD.pr_prodnum = DOBOM2.b2_prodnum
INNER JOIN DDORD
ON DOBOM2.b2_orid = DDORD.or_id
INNER JOIN DDTORD AS TBL2
ON DDORD.or_toid = TBL2.to_id
WHERE DOBOM2.b2_ordnum = TBL1.to_ordnum
) AS Total
FROM DDTORD AS TBL1
WHERE TBL1.to_trak2id IN (39, 40, 41)
AND TBL1.to_ordtype = 's'
AND TBL1.to_status = 'c'
GROUP BY TBL1.to_ordnum, TBL1.to_orddate
ORDER BY TBL1.to_ordnum DESC

However, without seeing DDL and sample inserts, it's impossible to test
whether this is what you meant.

HELP: "text and varchar are incompatible" agrivation!

I am trying to do a simple select statement on my db but keep getting the fallowing exception being thrown ...

System.Web.Services.Protocols.SoapException was unhandled
Actor=""
Lang=""
Message="System.Web.Services.Protocols.SoapException: Server was unable to process request. --> System.Data.SqlClient.SqlException: The data types text and varchar are incompatible in the equal to operator.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader()
at RelayService.ValidAccessID(String ID) in C:\Documents and Settings\Dean\My Documents\Visual Studio 2005\Projects\MsgSRX\MsgSRXService\App_Code\RelayService.vb:line 193
at RelayService.SendMessage(String msg, String AccessID, Int64& MsgID) in C:\Documents and Settings\Dean\My Documents\Visual Studio 2005\Projects\MsgSRX\MsgSRXService\App_Code\RelayService.vb:line 377
-- End of inner exception stack trace --"
Node=""
Role=""
Source="System.Web.Services"
StackTrace:
at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)
at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
at ChatClient.MsgSRX.RelayService.SendMessage(String msg, String AccessID, Int64& MsgID) in C:\Documents and Settings\Dean\My Documents\Visual Studio 2005\Projects\MsgSRX\ChatClient\Web References\MsgSRX\Reference.vb:line 342
at ChatClient.Form1.btnSend_Click(Object sender, EventArgs e) in C:\Documents and Settings\Dean\My Documents\Visual Studio 2005\Projects\MsgSRX\ChatClient\Form1.vb:line 13
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ButtonBase.WndProc(Message& m)
at System.Windows.Forms.Button.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.Run(ApplicationContext context)
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
at ChatClient.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81
at System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()

... Here is the source code to the method I am using ...

Private Function ValidAccessID(ByVal ID As String) As Boolean
Dim conn As New SqlConnection()
conn.ConnectionString = ConfigurationManager.ConnectionStrings("dbConnect").ConnectionString

Dim cmd As String
cmd = "SELECT AccessID, ExpireTime "
cmd &= "FROM AccessIDNumbers "
cmd &= "WHERE AccessID IN ('" & ID & "')"
' cmd &= "WHERE AccessID ='@.ID'"
Dim C As New SqlCommand(cmd, conn)
' C.Parameters.AddWithValue("@.ID", New SqlTypes.SqlString(ID))
' C.Parameters.Item("@.ID").SqlDbType = SqlDbType.Text

conn.Open()

Dim Count As Integer = 0
Dim reader As SqlDataReader = C.ExecuteReader
Dim expired As Boolean = False
If reader.HasRows Then
While reader.Read
Count += 1
Dim et As SqlTypes.SqlDateTime
et = SqlTypes.SqlDateTime.Parse(reader("ExpireTime"))
Dim ct As New SqlTypes.SqlDateTime(Now)
If ct > et AndAlso expired = False Then expired = True
End While
End If

conn.Close()
C.Dispose()
C = Nothing
conn.Dispose()
conn = Nothing

Return expired = False
End Function

... the problem is with the cmd &= "WHERE AccessID IN ('" & ID & "')" statement. How Can I get this to work properly!!! My data types for the AccessIDNumbers table are as fallows ..

UserName -> text -> nulls not allowedAccessID -> text -> nulls not allowedCreationTime -> datatime -> nulls not allowedExpireTime -> datatime -> nulls not allowedCurrentTable -> text -> nulls allowed

I don't understand why I'm getting this error!!!

Are you actually using 'text' columns, or 'varchar' columns?

|||I'm using text columns. What is the difference between text and varchar? They seem like they should be basically the same thing and as such the db engine should be able to compare them?|||

The 'text' datatype is a BLOB, which in short means that it is stored in a separate location than the rest of the data in the record. Instead of storing the actual value, the record stores a 16-bit pointer that directs SQL to the actual file that stores this data (by default, anyway. this can be changed). The max for this type of field is 2 GB.

'varchar,' on the other hand, is a standard type, which means it is stored with the actual record data. The limit for this type is 8,000 characters (=8 KB). This is actually preferable to a 'text' column if you are using 8,000 or fewer characters.

If you still want to use the text datatype, you may need to do conversions in your SQL statements (I haven't tried this--just a suggestion to see if you can get it to work).

' instead of thiscmd &="WHERE AccessID IN ('" & ID & "')"' try thiscmd &= "WHERE CAST(AccessID AS VARCHAR) ='" & ID & "'"