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

Quote:

---

[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 :D

Also, are stored procedures really THAT much more efficent?

Quote:

---

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

---

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

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

---

hm it worked...
1). why use nvarchar not varchar
2). What does the SELECT actually do?

and whoa... something worked for once, WOO! :)

Quote:

---

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

One thing about Dynamic TSQ query(the one using exec @sql), i was told by few that Dynamic TSQL query is not efficient and should be avoided as it affects the performance. Though i have not verified it with other developer.

I avoid using dynamic query anyway since most of my queries are too complex to be server by only one. I use dynamic query for Search stroed proedure and Deleting rows.

For Select, Update and Inser, i have seprate Proc for each table.

Just thought i will warn you :).

Have fun, happy learning.

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

---

Wait i think i found the solution

Quote:

---

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.

Quote:

---

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 + ''''

---

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 :D

Yeah the guid is causing this, i passed in a 0 and its fine. :mad:

Quote:

---

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 :D

---

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.

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

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.

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

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.

Quote:

---

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.

[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?

Quote:

---

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.

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

Quote:

---

Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '.'.

---

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?

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.

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.

Quote:

---

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

---

---

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

Quote:

---

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;
}

---

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?

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?