-
Aug 29th, 2004, 05:09 PM
#1
Thread Starter
Fanatic Member
Stored Procedures, msSQL
I figured I'd start a new thread for this
Ok, these things are frustrating the hell outa me! grrr...
ok, why would this not work
Code:
CREATE PROCEDURE sp_getTableWhere
@vTable varchar(20),
@vcharValue1 varchar(20),
@vcharValue2 varchar(20)
AS
SELECT * FROM @vTable
WHERE @vcharValue1 = @vcharValue2
Sais I need to declare @vTable
What im trying to do is select all records from a certain table by Value 1 and Value 2 where
Value1 is the Column, and Value 2 is the matching value for that column.
Problem #2)
I wrote this simple function to see if a user exists
Code:
CREATE PROCEDURE sp_isValidLogin
@charUsername varchar(50),
@charPHash varchar(50)
AS
if(DATALENGTH(@charUsername) = 0 AND DATALENGTH(@charPHash) = 0)
RETURN 0
if exists(SELECT * FROM tblUser
WHERE @charUsername = charUsername
AND
@charPHash = charPHash)
return 1
else
return 0
GO
When you call a return, does it terminate the stored procedure like a javascript or c++ statement would? Because it keeps giving me this message if @charUsername and @charHash are empty
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot use empty object or column names. Use a single space if necessary.
If i do pass in the value, and it does exist... SQL sais: Missing Default Property. What, huh? I thought returning 0 or 1 is enough?
Arghhhhh... i hate when things dont work
Last edited by invitro; Aug 29th, 2004 at 05:19 PM.
ok, so... windows takes 1 minute to search for a file on my PC yet google.com takes 1 second to search the entire internet?
-
Aug 29th, 2004, 05:23 PM
#2
Thread Starter
Fanatic Member
Also, are stored procedures really THAT much more efficent?
ok, so... windows takes 1 minute to search for a file on my PC yet google.com takes 1 second to search the entire internet?
-
Aug 29th, 2004, 05:32 PM
#3
Originally posted by invitro
Also, are stored procedures really THAT much more efficent?
Yes it IS!
What you are trying to do is create Dynamic Stored procedure. You can pass field name as variable to Stored Proc without any problem, but you cant pass Table Name as variable. If you want to do that you have to use following syntax.
Code:
CREATE PROCEDURE sp_getTableWhere
@vTable varchar(20),
@vcharValue1 varchar(20),
@vcharValue2 varchar(20)
AS
--Declaring variable to store our query
Declare sql nvarchar(500)
--assigning the query to our variable
@sql = 'SELECT * FROM @vTable WHERE ' + @vcharValue1 + '''' + '= ' + @vcharValue2 + ''''
--execute the dynamic sql query
exec(@sql)
return
[VBF RSS Feed]
There is a great war coming. Are you sure you are on the right side? Atleast I have chosen a side.
If I have been helpful, Please Rate my Post. Thanks.
This post was powered by :
-
Aug 29th, 2004, 05:41 PM
#4
Thread Starter
Fanatic Member
When I execute the above I get a nvarchar is not a recognized cursor option. Must declare @sql
...
also shouldent the dynamic query look like this
Code:
@sql = 'SELECT * FROM ' + @vTable + ' WHERE ' + @vcharValue1 + '''' + '= ' + @vcharValue2 + ''''
Notice the ' + + ' where @vTable is
Last edited by invitro; Aug 29th, 2004 at 05:45 PM.
ok, so... windows takes 1 minute to search for a file on my PC yet google.com takes 1 second to search the entire internet?
-
Aug 29th, 2004, 05:50 PM
#5
Sorry was in a hurry so few mistakes. Code highlighted are the one i missed out.
Here :
Code:
CREATE PROCEDURE sp_getTableWhere
@vTable varchar(20),
@vcharValue1 varchar(20),
@vcharValue2 varchar(20)
AS
--Declaring variable to store our query
--I missed out the @ here
Declare @sql nvarchar(500)
--assigning the query to our variable
--you need select variable name, to assign variable
select @sql = 'SELECT * FROM ' + @vTable + ' WHERE ' + @vcharValue1 + '= ' + '''' + @vcharValue2 + ''''
--execute the dynamic sql query
exec(@sql)
return
Last edited by Danial; Aug 29th, 2004 at 05:53 PM.
[VBF RSS Feed]
There is a great war coming. Are you sure you are on the right side? Atleast I have chosen a side.
If I have been helpful, Please Rate my Post. Thanks.
This post was powered by :
-
Aug 29th, 2004, 05:55 PM
#6
Thread Starter
Fanatic Member
hm it worked...
1). why use nvarchar not varchar
2). What does the SELECT actually do?
and whoa... something worked for once, WOO!
Last edited by invitro; Aug 29th, 2004 at 06:00 PM.
ok, so... windows takes 1 minute to search for a file on my PC yet google.com takes 1 second to search the entire internet?
-
Aug 29th, 2004, 06:03 PM
#7
Originally posted by invitro
hm it worked...
1). why use nvarchar not varchar
2). What does the SELECT actually do?
1. you could use varchar, nvarchar allows you to store chas which are not standard such as alphabets from different language. Though it takes up double the memory then varchar. Find out about data type and use then to your need as that will make your query more efficient
2. Select is used to asssign value to a variable its just the TSQL syntax, in say vb you would say
x = 1 where as in TSQL you would have to use
select x=1 (note that a variable in TSQL must be prefiiexed with @
Hope this helps.
Danial
[VBF RSS Feed]
There is a great war coming. Are you sure you are on the right side? Atleast I have chosen a side.
If I have been helpful, Please Rate my Post. Thanks.
This post was powered by :
-
Aug 29th, 2004, 06:07 PM
#8
-
Aug 29th, 2004, 06:56 PM
#9
Thread Starter
Fanatic Member
I keep gettin
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver]Syntax error or access violation
when i try to run this:
Code:
CREATE PROCEDURE sp_getTableWhere
@vTable varchar(25),
@vcharValue1 varchar(25),
@vcharValue2 varchar(25)
AS
--Declaring variable to store our query
Declare @sql nvarchar(500)
--assigning the query to our variable
SELECT @sql = 'SELECT * FROM ' + @vTable + ' WHERE ' + @vcharValue1 + '= ' + '' + @vcharValue2 + ''
--execute the dynamic sql query
EXEC(@sql)
return
GO
ok, so... windows takes 1 minute to search for a file on my PC yet google.com takes 1 second to search the entire internet?
-
Aug 29th, 2004, 07:03 PM
#10
Thread Starter
Fanatic Member
Wait i think i found the solution
SYMPTOMS
When you use the Microsoft ODBC Driver for SQL Server 2000, you receive the "Syntax Error Or Access violation" error message when you submit a stored procedure parameter that ends with the three ASCII characters in the following exact sequence: 0x0D, 0x1C, and 0x0D.
RESOLUTION
A supported fix is now available from Microsoft, but it is only intended to correct the problem described in this article. Only apply it to systems that are experiencing this specific problem. This fix may receive additional testing to further ensure product quality. Therefore, if you are not severely affected by this problem, Microsoft recommends that you wait for the next Microsoft Data Access Components (MDAC) 2.8 that contains this fix.
Im passing in a guid as one of the parameters... yep, its the same guid() problem. Wonedr why it keeps inserting junk at the end of my guids? Anyway, snipping time.
ok, so... windows takes 1 minute to search for a file on my PC yet google.com takes 1 second to search the entire internet?
-
Aug 29th, 2004, 07:06 PM
#11
SELECT @sql = 'SELECT * FROM ' + @vTable + ' WHERE ' + @vcharValue1 + '= ' + '' + @vcharValue2 + ''
[/code] [/B]
You have two single quote('') instead of four. (''''). Note they are not double quote, they are four single qute.
copy the following and try.
Code:
SELECT @sql = 'SELECT * FROM ' + @vTable + ' WHERE ' + @vcharValue1 + '= ' + '''' + @vcharValue2 + ''''
[VBF RSS Feed]
There is a great war coming. Are you sure you are on the right side? Atleast I have chosen a side.
If I have been helpful, Please Rate my Post. Thanks.
This post was powered by :
-
Aug 29th, 2004, 07:13 PM
#12
Thread Starter
Fanatic Member
Sorry still dosent work
I even cut the guid, and it gives me the same error...
Actually, after cutting the guid it gives me another error
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '-'.
What does Line1 mean... is it saying theres a error in the Stored Procedure or somewhere in my code.
I'm going insane
ok, so... windows takes 1 minute to search for a file on my PC yet google.com takes 1 second to search the entire internet?
-
Aug 29th, 2004, 07:18 PM
#13
Thread Starter
Fanatic Member
Yeah the guid is causing this, i passed in a 0 and its fine.
ok, so... windows takes 1 minute to search for a file on my PC yet google.com takes 1 second to search the entire internet?
-
Aug 29th, 2004, 07:20 PM
#14
Originally posted by invitro
Sorry still dosent work
I even cut the guid, and it gives me the same error...
Actually, after cutting the guid it gives me another error
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '-'.
What does Line1 mean... is it saying theres a error in the Stored Procedure or somewhere in my code.
I'm going insane
First of all I assumed those two fields you are comparing is String, if you have those field as number/integer then you wouldnt need to wrap them with ''''.
Firstly simplify things, dont use dynamic query, learn how to use the normal query then move one to dynamic ones.
Write the query seperately.
e.g
Code:
CREATE PROCEDURE sp_getTableWhere
@value varchar(25)
AS
SELECT * FROM YourTable WHERE FieldName=@value
return
GO
Try it and then convert the field name to a variable and so on.
Learn how to execute query in SQL server and Query Analyser, you can test your query there.
Hope this helps.
[VBF RSS Feed]
There is a great war coming. Are you sure you are on the right side? Atleast I have chosen a side.
If I have been helpful, Please Rate my Post. Thanks.
This post was powered by :
-
Aug 29th, 2004, 08:05 PM
#15
Thread Starter
Fanatic Member
See, the problem isent the query... something else is going on here. I wrote a function to trim the guid so its a number without the { and -'s
D479F7E0EAAC46C4ADEB450153EAD5C4
now, when i do a query through query analyzer...
Code:
sp_getTableWhere tblUser, charUsername, 'myname'
it works, selects the entire row... yay
now, I do the EXACT same function but i change two fields
Code:
sp_getTableWhere tblUser, guidUserID, 'D479F7E0EAAC46C4ADEB450153EAD5C4'
It dosent find anything... i konw that is the right guid. I checked 20 times, even compared it to the one stored in the database... but it wont find it in the query analyzer.
I even made a query using the username, and copied the GUID that was displayed in the field... then when i went to use it, it didnt find anything.
However, doing
Code:
SELECT * FROM tblUser WHERE guidUserID = 'D479F7E0EAAC46C4ADEB450153EAD5C4
'
Does work.. so, something is going on with the SP
Last edited by invitro; Aug 29th, 2004 at 08:13 PM.
ok, so... windows takes 1 minute to search for a file on my PC yet google.com takes 1 second to search the entire internet?
-
Aug 29th, 2004, 08:10 PM
#16
Since you declared your variable as 20 chars :
@vcharValue1 varchar(20),
@vcharValue2 varchar(20)
and your GUID is longer then 20 chars, i suspect it is truncuating the string thats why you are not finding it.
change the size to 100 and try it.
[VBF RSS Feed]
There is a great war coming. Are you sure you are on the right side? Atleast I have chosen a side.
If I have been helpful, Please Rate my Post. Thanks.
This post was powered by :
-
Aug 29th, 2004, 08:14 PM
#17
Thread Starter
Fanatic Member
OMG... why didnt i see that. God thanks again for saving me the headacke, wow...
*sigh* yea works great, Danial to the rescue again. Thanks, you've been a huge help, your my hero for today man. (and yes back to your previous comment, i AM thinking of migrating to .net)
hehe
ok, so... windows takes 1 minute to search for a file on my PC yet google.com takes 1 second to search the entire internet?
-
Aug 29th, 2004, 09:02 PM
#18
Thread Starter
Fanatic Member
Before I forget.. im passing in an email address as a nvarchar
its giving me an error on the . in the email address. Whats the deal? how can i avoid that.
ok, so... windows takes 1 minute to search for a file on my PC yet google.com takes 1 second to search the entire internet?
-
Aug 29th, 2004, 09:17 PM
#19
Originally posted by invitro
Before I forget.. im passing in an email address as a nvarchar
its giving me an error on the . in the email address. Whats the deal? how can i avoid that.
That shouldnt be any problem. What error are you getting? You can pass anything as long as you have passed it as nvarchar/string type.
[VBF RSS Feed]
There is a great war coming. Are you sure you are on the right side? Atleast I have chosen a side.
If I have been helpful, Please Rate my Post. Thanks.
This post was powered by :
-
Aug 29th, 2004, 09:24 PM
#20
Thread Starter
Fanatic Member
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '.'.
i know its the email because when i use the same function with another string it works.
This is what happened with the guid.. with the guid it had a problem with the -
would it matter that its a varchar not a nvarchar?
ok, so... windows takes 1 minute to search for a file on my PC yet google.com takes 1 second to search the entire internet?
-
Aug 29th, 2004, 09:29 PM
#21
Originally posted by invitro
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '.'.
i know its the email because when i use the same function with another string it works.
This is what happened with the guid.. with the guid it had a problem with the -
would it matter that its a varchar not a nvarchar?
It shouldnt matter whether you use varchar or nvarchar.
What exactly are you doing? Running select query? Inserting ?
If its a select query I suspect you are not wrapping it with ' '
Give me more details on how and what you are doing?
I gotta hit the bed now, its 3.30 am here, will answer tomorrow morning.
[VBF RSS Feed]
There is a great war coming. Are you sure you are on the right side? Atleast I have chosen a side.
If I have been helpful, Please Rate my Post. Thanks.
This post was powered by :
-
Aug 29th, 2004, 09:49 PM
#22
Thread Starter
Fanatic Member
im using our getTableWhere function
Code:
CREATE PROCEDURE sp_getTableWhere
@vTable varchar(50),
@vcharValue1 varchar(50),
@vcharValue2 varchar(50)
AS
--Declaring variable to store our query
Declare @sql nvarchar(500)
--assigning the query to our variable
SELECT @sql = 'SELECT * FROM ' + @vTable + ' WHERE ' + @vcharValue1 + ' = ' + '''' + @vcharValue2 + ''''
--execute the dynamic sql query
EXEC(@sql)
return
GO
It works great for anything that dosent have odd characters like periods and such... but the second i put an email address in it gives me that. Maybe it has a problem with the @
heres the error
Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '.'.
Last edited by invitro; Aug 29th, 2004 at 09:59 PM.
ok, so... windows takes 1 minute to search for a file on my PC yet google.com takes 1 second to search the entire internet?
-
Aug 30th, 2004, 08:59 PM
#23
Did you manage to get it working? Like i said you should not have any problem passing @
How are you passing the parameter to the proc?
[VBF RSS Feed]
There is a great war coming. Are you sure you are on the right side? Atleast I have chosen a side.
If I have been helpful, Please Rate my Post. Thanks.
This post was powered by :
-
Aug 30th, 2004, 09:10 PM
#24
Thread Starter
Fanatic Member
No, it wont work... i had to do this to get it working, but it only works for a single SP function. I updated my avatar to reflect my mood. HAH! Ok, I'm going to load a bunch of code on you here is how I do everything:
Code:
function sCart_GetRecordset(strSQL)
{
if (!this.m_objConn)
this.m_objConn = this.GetConnection();
var objRS = Server.CreateObject("ADODB.RecordSet");
objRS.ActiveConnection = this.m_objConn;
objRS.CursorLocation = adUseClient;
objRS.LockType = adLockBatchOptimistic;
objRS.Open(strSQL);
return objRS;
}
I pass my record set through this function.
sCart is an object that I created and can call functions on a fly.
Anyhow, in order to use the function above I pass in something like this
Code:
strSQL = 'INSERT INTO testTable WHERE blah blah...";
this.GetRecordset(strSQL);
The function I am using to pass in the insert is this:
Code:
var strGuid = this.getGuid();
var pHash = objSI.md5.getHash(strPassword);
strSQL = "EXECUTE sp_insertUser ";
strSQL += "\'" + strGuid + "\',2,\'" + strUser;
strSQL += "\',\'" + pHash + "\',\'";
strSQL += strFullname + "\',\'" + strAddress + "\',\'" ;
strSQL += strCity + "\',\'" + strCountry + "\',\'" + strPostal + "\',\'";
strSQL += strPhone + "\',\'" + strWPhone + "\',\'";
strSQL += strCPhone + "\',\'" + strFax + '\',\'' + strEmail + '\',\'';
strSQL += strActivation + "\',0";
Response.Write(strSQL);
this.ExecuteCommand(strSQL);
Theoreticaly, it is suppose to call EXECUTE sp_insertUser from the database. However, sp_insertUser dosent like when I pass in a . or a & or anything like that. Here is sp_insertUser
Note that the insertUser function DOES work if i leave out any wierd characters such as periods and percents.
Code:
CREATE PROCEDURE sp_insertUser
@guidUserID varchar(32), @intAccessID smallint, @charUsername varchar(30),
@charPHash varchar(50), @charEmail varchar(50), @charFullname varchar(50),
@charStreetAddress varchar(50), @charCity varchar(25), @charCountry varchar(25),
@charPostalCode varchar(9), @charPhone varchar(15), @charWorkPhone varchar(15),
@charCellPhone varchar(15), @charFax varchar(15), @charActivation varchar(4),
@blnActivated tinyint
AS
INSERT INTO tblUser (guidUserID, intAccessID, charUsername, charPHash, charFullname, charStreetAddress,
charCity, charCountry, charPostalCode, charPhone, charWorkPhone, charCellPhone, charFax,
charEmail, charActivation, blnActivated, datCreationDate)
VALUES (@guidUserID, @intAccessID, @charUsername,
@charPHash, @charFullname,
@charStreetAddress, @charCity, @charCountry,
@charPostalCode, @charPhone, @charWorkPhone,
@charCellPhone, @charFax, '''' + @charEmail + '''',@charActivation,
@blnActivated, getdate())
return
GO
As you will notice, I added the \' escape characters when im making the strSQL += statement. I thought this would work sincei it worked for my other function.
Code:
CREATE PROCEDURE sp_getTableWhere
@vTable varchar(50),
@vcharValue1 varchar(50),
@vcharValue2 varchar(50)
AS
--Declaring variable to store our query
Declare @sql nvarchar(500)
--assigning the query to our variable
SET @sql = "SELECT * FROM " + @vTable + " WHERE " + @vcharValue1 + " = " + '''' + @vcharValue2 + ''''
--execute the dynamic sql query
EXEC(@sql)
return
GO
The above function will behave exactly the same, for instance if I use this statement:
Code:
function sCart_getRecordWhere(tblName, strCol, strVal)
{
strSQL = 'sp_getTableWhere ' + tblName + ',' + strCol + ',' + strVal + ';
return this.GetRecordset(strSQL);
}
...if theres a . or $ in strVal, it will give me that Line1 error bs. However, when I pass in the values like so:
Code:
function sCart_getRecordWhere(tblName, strCol, strVal)
{
strSQL = 'sp_getTableWhere ' + tblName + ',' + strCol + ',\'' + strVal + '\'';
return this.GetRecordset(strSQL);
}
notice the \' chars by strVal, it works. SO, I figured I could do that for the insertUser function, but this wont work. It still gives me an error. I even tried adding the escape characters to @charEmail but no luck. This is really frustrating since im on a deadline, but thats my problem... i just wish i was more expirienced in SP's.
Last edited by invitro; Aug 30th, 2004 at 09:14 PM.
ok, so... windows takes 1 minute to search for a file on my PC yet google.com takes 1 second to search the entire internet?
-
Aug 30th, 2004, 09:24 PM
#25
I am getting a bit confused, when are you getting the error. When executing Select or Insert? How are you passing the parameter to the Insert query?
Have tried executing the quer from query manager with @ in it? That will tell you whether you have problem with your proc or the way you are passing parameter.
If thats the case i would suggest you use Command Object instead, look at me example i posted on your other thread.
Also post your connection string.
Originally posted by invitro
Code:
CREATE PROCEDURE sp_insertUser
@guidUserID varchar(32), @intAccessID smallint, @charUsername varchar(30),
@charPHash varchar(50), @charEmail varchar(50), @charFullname varchar(50),
@charStreetAddress varchar(50), @charCity varchar(25), @charCountry varchar(25),
@charPostalCode varchar(9), @charPhone varchar(15), @charWorkPhone varchar(15),
@charCellPhone varchar(15), @charFax varchar(15), @charActivation varchar(4),
@blnActivated tinyint
AS
INSERT INTO tblUser (guidUserID, intAccessID, charUsername, charPHash, charFullname, charStreetAddress,
charCity, charCountry, charPostalCode, charPhone, charWorkPhone, charCellPhone, charFax,
charEmail, charActivation, blnActivated, datCreationDate)
VALUES (@guidUserID, @intAccessID, @charUsername,
@charPHash, @charFullname,
@charStreetAddress, @charCity, @charCountry,
@charPostalCode, @charPhone, @charWorkPhone,
@charCellPhone, @charFax, '''' + @charEmail + '''',@charActivation,
--Highlighted bits are not needed
@blnActivated, getdate())
return
GO
[VBF RSS Feed]
There is a great war coming. Are you sure you are on the right side? Atleast I have chosen a side.
If I have been helpful, Please Rate my Post. Thanks.
This post was powered by :
-
Aug 30th, 2004, 09:31 PM
#26
Thread Starter
Fanatic Member
Yeah the statement executes in Query Manager fine.
Ok, so to simply things, heres how im passing it in.
Code:
var strGuid = this.getGuid();
var pHash = objSI.md5.getHash(strPassword);
strSQL = "EXECUTE sp_insertUser ";
strSQL += "\\'" + strGuid + "\\',2,\\'" + strUser;
strSQL += "\\',\\'" + pHash + "\\',\\'";
strSQL += strFullname + "\',\'" + strAddress + "\\',\\'" ;
strSQL += strCity + "\\',\\'" + strCountry + "\\',\\'" + strPostal + "\\',\\'";
strSQL += strPhone + "\\',\\'" + strWPhone + "\\',\\'";
strSQL += strCPhone + "\\',\\'" + strFax + "\\',\\'" + strEmail + "\\',\\'";
strSQL += strActivation + "\\',0";
Response.Write(strSQL);
this.ExecuteCommand(strSQL);
After I run that function, I get
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '.'.
/shopping_cart/CartDatabase.asp, line 88
Line 88 is this line:
Code:
function sCart_GetRecordset(strSQL)
{
if (!this.m_objConn)
this.m_objConn = this.GetConnection();
var objRS = Server.CreateObject("ADODB.RecordSet");
objRS.ActiveConnection = this.m_objConn;
objRS.CursorLocation = adUseClient;
objRS.LockType = adLockBatchOptimistic;
LINE 88> objRS.Open(strSQL);
return objRS;
}
ok, so... windows takes 1 minute to search for a file on my PC yet google.com takes 1 second to search the entire internet?
-
Aug 30th, 2004, 09:38 PM
#27
This is not the propery way to execute Stored procedure. The way you are doing it you might as well use Embeded SQL insert query!
Define SQL parameter object and then pass it to the Stored Proc, or use the way i posted, it will work fine.
Did you read my PM?
[VBF RSS Feed]
There is a great war coming. Are you sure you are on the right side? Atleast I have chosen a side.
If I have been helpful, Please Rate my Post. Thanks.
This post was powered by :
-
Aug 31st, 2004, 07:26 PM
#28
Thread Starter
Fanatic Member
D,
After an hour of searching online, I have not found any tutorials that teach how to properaly execute a Stored Procedure in Javascript. What we discussed earlier does not work.
Any other ideas?
ok, so... windows takes 1 minute to search for a file on my PC yet google.com takes 1 second to search the entire internet?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|