|
Why don't you try along these lines:
CREATE PROCEDURE [dbo].[SelectItemList]
@ColName string,
@FieldName string,
@FieldValue int
AS
BEGIN TRANSACTION
SELECT @ColName FROM ItemList where @FieldName = @FieldValue
COMMIT TRANSACTION
[EDIT: this doesn't solve it. The message by Michael Potter holds the answer.[/EDIT]
modified 29-Jun-12 9:41am.
|
|
|
|
|
Thanks for the reply but it didn't work out. I am restating my problem with following table.
I have one table named ItemList like this:
ID Name Price($)
1 Bag 20
2 Fabrics 35
3 Tools 100
now my query was
CREATE PROCEDURE [dbo].[SelectItemList]
@ColName string,
@ID int
AS
BEGIN TRANSACTION
SELECT @ColName FROM ItemList where ID = @ID
COMMIT TRANSACTION
I need output something like this. If I have @ID = 1 and @ColName = 'Name', then my output should be
Name
-----
Bag
I tried my way that i posted earlier and your way, but it didn't work out. It gave output like this instead.
Column1
--------
Name
Name
Is there any other way or am I doing any wrong in my query?
Please help.
suchita
|
|
|
|
|
You'd need to introduce the output-keyword to one of the parameters, as described in the documentation[^].
Your sproc is a wrapper around a simple statement, adding complexity without any benefits. The design, the approach, it's wrong. There's nothing to "commit" to the database, and a simple select-statement (with parameters) would be sufficient.
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
Thanks Eddy, I will take that commit out from my query. I mistakenly forgot to take that out. But my question is still the same. I don't know whelther we can send fieldname as input parameter to get the value of that field.I know it looks very weird for that small table but I have to use the same concept for my huge database system. I thought that table would be easy for me to explain what my output should look like. I am going through the documentation you sent me but at glance, i couldn't see what I am looking for but I am going through it line by line. Thanks for your time .
suchita
|
|
|
|
|
Dhyanga wrote: I don't know whelther we can send fieldname as input parameter to get the value of that field.
You could join on the system-tables, but to what use? You'll be concatenating constants in a way that can hardly be considered helpfull. If writing Sql is that much of a problem, consider a ORM-framework.
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
I had got the required output simply writing the sql query but I wanted it on stored procedure. I know i can do it something like this in my query.
string colname;
int ID;
string query;
.
.
.
query = "select " + colname + " from ItemList where ID = " + ID ;
SqlCommand cmd = new SqlCommand(query, sqlconn);
.
.
.
.
This had solved my problem but I wanted it on stored procedure. and I was stuck giving the column name itself as input parameter.
suchita
|
|
|
|
|
I can only point to jschells' comment
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
Thanks Eddy for your time and views.
suchita
|
|
|
|
|
Dhyanga wrote: I don't know whelther we can send fieldname as input parameter
You can't.
Your choices are
1. Create the SQL dynamically in C# and then execute it.
2. Create the SQL dynamically in SQL and then execute it in SQL.
3. Use a fixed set of fixed SQL statements and select one based on the data passed in.
|
|
|
|
|
Thanks Jschell.. Just curious if that can be possible. If not, i can just execute it without using stored procedure. I was trying to use better way than that if I could...
suchita
|
|
|
|
|
Dhyanga wrote: i can just execute it without using stored procedure
My solutions work with or without stored procs.
|
|
|
|
|
Hi,
try like this..
CREATE PROCEDURE [dbo].[SelectItemList]
@ColName string,
@ID int
AS
BEGIN TRANSACTION
SELECT @ColName = ColumnName FROM ItemList where ID = @ID
COMMIT TRANSACTION
Karthik Harve
|
|
|
|
|
Am I missing something, you need dynamic SQL to meet this requirement, you solution does not work!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
You have to use sp_executesql and construct the query string yourself. Do realize that you can open yourself up to SQL injection attacks if you don't properly test the variables.
CREATE PROCEDURE SelectItemList
(
@ColName string,
@ID int
)
AS
DECLARE @sql NVARCHAR(4000)
SET @sql = N'SELECT ' + @ColName + N' FROM ItemList where ID = ' + CAST(@ID AS VARCHAR(10))
EXEC sp_executesql @sql
|
|
|
|
|
Thank you Micheal. That's what I wanted but we need to give the @ColName nvarchar size too. It worked fantastic.
suchita
|
|
|
|
|
|
Keep in mind that as formatted that solution is open to SQL injection attacks.
|
|
|
|
|
Hi All,
How we make the .mdf file of a database ?
I want a script or C# code that could be used in a utility in C#.Net.
Please help !!
Regards,
Lalit
modified 27-Jun-12 4:58am.
|
|
|
|
|
lalitN wrote:
How we make the .mdf file of a database ?
That extension is used both for Sql Server as well as Microsoft Access. Which are you targetting?
Bastard Programmer from Hell
|
|
|
|
|
I am using SQL Server 2008 R2.
|
|
|
|
|
The command to create a database is documented here[^], examples are at the bottom of the page.
Bastard Programmer from Hell
|
|
|
|
|
Hi,
Can we create the mdf file by taking the backup of one existing database ?
Regrds,
Lalit
|
|
|
|
|
Yes. Backup the existing database, and restore [^] it. Keep in mind that this procedure will convert "older" database versions (backups from 2005) to the format of your server (2008R2).
Bastard Programmer from Hell
|
|
|
|
|
Hi, everyone, I'm using sqlite 3.6.22 in an ARM-linux based application, and I was annoyed a lot by a memory-leak problem.
this is the problem:
sqlite3_free_table(char **result) doesn't exactly release the memory allocated by sqlite3_get_table function.
To verify it I made it called dirrectly after sqlite3_get_table() function, but I can still access the azResult parameter.
bellow is part of my code:
sqlite3 *db;
char *sqlcmd;
int nrow ;
int ncolumn ;
char **azResult;
char *zErr;
int rc;
rc = sqlite3_open("/usr/YY_AEACard.db", &db);
if (rc) {
fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
sqlite3_close(db);
return -1 ;
}
sqlcmd = sqlite3_mprintf("select CardID,CardType,BgnDate,EndDate,ValidTimes,CardRight,CardFlag, EnabledPorts,LastAccessPort,CardPsw from tb_CardInfo where CardID=%d and EnabledPorts = %d\n",card->CardID, nPort );
sqlite3_get_table( db, sqlcmd, &azResult, &nrow, &ncolumn, &zErr );
sqlite3_free( sqlcmd );
///////////////////////////////////////////////////////////
sqlite3_free_table(azResult);
///////////////////////////////////////////////////////////
// it still works when i do this here.
if (rc != SQLITE_OK) {
if (zErr != NULL) {
fprintf(stderr, "SQL error: %s\n", zErr);
sqlite3_free(zErr);
}
}
if(nrow <= 0)
{
printf("No data from db!\n");
return -1;
}
if(ncolumn < 9)
{
printf("Columns are not enough for card table!\n");
return -1;
}
bzero(card, sizeof(card));
card->CardID = atoi(azResult[ncolumn+0]);
card->CardType = atoi(azResult[ncolumn+1]);
strcpy(card->BgnDate, azResult[ncolumn+2]);
strcpy(card->EndDate, azResult[ncolumn+3]);
card->ValidTimes = atoi(azResult[ncolumn+4]);
card->CardRight = atoi(azResult[ncolumn+5]);
card->CardFlag = atoi(azResult[ncolumn+6]);
card->EnabledPorts = atoi(azResult[ncolumn+7]);
card->LastAccessPort = atoi(azResult[ncolumn+8]);
strcpy(card->CardPsw, azResult[ncolumn+9]);
sqlite3_free_table(azResult);
//this is a test for the memory leak
printf("%s\n", azResult[4]);
//funny enough, it still works here.
sqlite3_close(db);
return 0;
please help me, thank you.
|
|
|
|
|
There is no guarantee that the memory pointed to is overwritten when it is released. In my experience, only a very few debugging memory managers bother to overwrite released memory blocks. So what you observe is not evidence of a memory leak. Your system may have some functions to query the memory manager for such things as the total allocated memory size.
Cheers,
Peter
Software rusts. Simon Stephenson, ca 1994. So does this signature. me, 2012
|
|
|
|