Using sql_variant as a parameter
-
I have to write a proc that updates constants used in my website.
On my ASP page I know what kind of constant it is from the prefix eg acstr = string/varchar, acb = bit, acint = int, acnum = numeric but I dont want to have lots of different parameters for the proc and only fill one at a time leaving the others blank.
So I am thinking of using just one parameter that will be a sql_variant.
I have never used a sql_variant before and what I am asking is how do I format the value of the parameter when passing it into the proc.
eg in my ASP code
sysConstantName = "acstrSiteName"
varConstantValue = "Test Site"
strSQL = "EXEC usp_asp_update_system_constant " &_
"@SysConstant = '" & SQLReady(sysConstantName,50) & "'," &_
"@Value = '" & SQLReady(varConstantValue,1000) & "';"
where SQLReady function replaces single ' with '' and trims to the length provided.
Is it a case of if I know its a string Im passing in then wrap the value parameter in quotes and if its numeric don't or do sql_variants have to be formated in a particular way?
Thanks for any help in advance.
-
If you use the parameters collection of the ado command object then you don't have to worry about wrapping strings in quotes. been a while since I did asp, but...
cmd.Command = 'usp_asp_update_system_constant'
cmd.Parameters.Add ... (can't remember syntax here)
cmd.execute
Also, since you're using parameters instead of building up a string, you're less likely to be hit with SQL injection.
I personally recomend against generic procs that do a lot of things. It's better to have procedures that do specific things, but if you insist, make the parameter of type varchar. Everything else (dates, int, float, bit) can be cast from varchar without problem. Don't use sql_varient. They're messy, large, have overheads
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Thanks for the response.
The reason I am doing it as a string built up is that alot of our procs have optional parameters and as far as I know you have to define all params with the command obj whereas with the string I only pass in those that I am using.
Also the proc is not a generic proc doing lots of things its a proc doing one distinct thing changing the value of a system constant used by the website. The problem is that these constants are of different types therefore I only want to have one parameter for the value rather than lots of params for each different data type.
I could set a lot of params up for each different data type and set their defaults to null and then only pass in the param for the type needed but I read an article about sql_variant and thought it might be good for the job. Its is only going to be used as a temporary data store and then the value will be converted to the correct type on update. Why is it that they are so messy?
-
The reason I am doing it as a string built up is that alot of our procs have optional parameters and as far as I know you have to define all params with the command obj whereas with the string I only pass in those that I am using.
No you don't, just the ones that are required (don't have default values)
I only want to have one parameter for the value rather than lots of params for each different data type.
So use a varchar. (since you're doing a conversion anyway)
They're messy for the same reason that varients are messy in VB.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
All right Ming tell me then how you would do this in a way that so when
new constants are added to the table (almost daily) I wouldn't have to go in
and write a seperate stored proc for that constant or add another param into the
proc/ update the ASP code.
I have a tbl_CONSTANTS which has 60+ columns expanding everyday. Each row represents
a different sites settings.
When the data changes in the table or a new column/constant is added the admin person just
goes to the Generate Site Constants page hits a button and that sites / or all sites constant
file is rewritten out containing the new constants/values ready to be used in the site.
As my boss has asked me to write a CMS for some sites so that they can change some of the values
for these constants themselves as they affect things like the no of jobs displayed on front page,
whether to display a thumbnail image for a news item, whether to display full or partial job details etc etc.
I want to write a piece of code that will be able to change the constant value whatever that constant is and not
worry about having to maintain it whenever a new constant is added.
I could in my ASP CMS class just build up an sql string to Update that constant in the table using the name and the value. sql = "UPDATE tbl_CONSTANTS SET " & strConstantName & " = '" & strConstantValue & "' where..."
But I want a proc and just grant exec permissions to it for that user as I dont have any
update or insert code in my ASP. So I would like a way to be able to update these values without having
to edit the proc/code everyday a new constant is added to the table.
So how would you suggest I accomplish that task as set out my superiors in the best way Mr Celko?
-
A table design that doesn't require constant changing would be a good place to start. This is also violating relational concepts a fair bit, but I can't think of a better way right now.
CREATE TABLE SiteConstants (
SiteName VARCHAR(20),
ConstantName VARCHAR(20),
ConstantValue VARCHAR(20)
)
GO
ALTER TABLE Siteconstants ADD CONSTRAINT pk_SiteConstants PRIMARY KEY (SiteName, ConstantName)
GO
CREATE PROCEDURE AddConstant (@SiteName VARCHAR(20), @ConstName VARCHAR(20), @ConstValue VARCHAR(20))
AS
INSERT INTO SiteConstants (SiteName,ConstantName,ConstantValue)
VALUES (@SiteName, @ConstName, @ConstValue)
GO
-- ... etc for update, delete, etc
At least this way, you won't have rows mostly null (which is always an indication that the design is incorrect) and you won't be altering the table several times a day.
btw, don't mind Joe, he's always like that. In fact, he was quite mellow today.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Yeah he can be a bit funny cant he.
I dont have any rows full of nulls in my table at the moment.
Every row has a value for every column and that column is the constant.
Its not supposed to change daily but as my PM is always asking for new constants so that we can customize that companies particular website.
I just want a way that I can update whatever column it is without rewriting the procs/client code calling it.
As the person above says if I pass the value in as a varchar and the type it is I can have some dynamic sql in the proc that updates the value for that constant.
-
Joe Joe Joe
Look the constants don’t change every day. But new ones are wanted to make the sites we are doing even more customisable than they already are.
So when my project manager says I want to be able to turn the display of national flags on/off for a particular sites job results. I have to add that new constant into the table.
SiteA may have it turned on, SiteB may have it turned off. In the future someone from SiteBs company may ring up and say "Actually I would like to have Flags turned on for job results" Then all the admin person has to do is change the value for that site to on and regenerate the constant file that is used
by the website.
The system works very well at the moment Joe and our company makes a hell of a lot of money selling them so whether the concepts used in the site fit into what you think is right or wrong is neither here nor there. I know u r a RDBMS purist and freak out at anything that does not conform to standards you adhere to as I've read a couple of your books. And like most people who have read your books half agree with some of the things you say and the other half don't agree with most of the things you say.
Maybe if the standards inspector from ISO came round to inspect our databases he might issue a fixed penalty notice or even shut us down for having prefixes on our objects such as tbl_ . But it was decreed by our IT dept and it really does help a lot to distinguish between objects when doing Exports/Imports in SQL Server. And just to make you really sick I also use usp_ for my stored procs and udf_ for my functions. (Hushed silence of shock n horror)
In the real world where people do use auto increment identity fields for Primary keys.....(did your jaw drop there Joe? I bet it did.) I am given a task by my John Wayne lookalike boss and I have to accomplish that task in the given time period given to earn a wage to feed myself and my 12 little cockney chimney sweeping kids
I really don’t think they can take it much more.So maybe I should have a separate DB for every Jobboard site that we do and just have a constants.vbs.inc file with all the settings that the site needs but that would be a hell of a lot of work to do to set each one up. And to be honest I think my boss is pretty god damn happy that at the moment we can set a new site up in less than five minutes at the press of a button. You see it replicates a template set of data (eg creates new site record, default constants, default settings, test client/cand record and admin account plus many many other interesting things), then it creates the folders and copies the website files into wwwroot, Sets up and configures a new website in IIS (eg sets the host headers, creates virtual directories and gives the IUSR persmissions on set folders blah blah).
Once that is done the admin person can go and configure any differences or particular requirements that the site needs mainly by just changing the values in this horrifically badly designed and totally disgracefully named constant table.
It really is quite good Joe but I am imagining you would probably have a coma inducing fit if you saw beneath the bonnet, but to be honest that is a minor because our Jobboards have been in business for 7 years we have hundreds of sites and it all runs smoothly....well so far your probably imagining that it is teetering on the brink of massive irreversible corruptible breakdown but its not I promise
So if you really think you know best you can apply for a job here if you want, we are looking for a new person to replace the bloke who has to answer the phone and set up the websites. He's leaving as there’s not much work to do anymore now that the auto-website-set up page has been live for a while.
But if you do have any good suggestions on the problem that I posed without being condescending or sarcastic (see I
can do it as well but I'm English so we've had more practise) then please do put them forward. But giving it the big I am does nothing to prove your point and if I ever met you in the pub and you spoke to me like you have in person I would probably chin you, but then we are all friends at SQLServerCentral.com so lets have none of that shall we
So have a nice day as they say in America.
-
Rob,
All I have to say is that I haven't laught this much in a loooooong time
* Noel -
Thanx noeld im glad someone is happy with something I have done
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply