Problem passing columns as parameter!

Dear friens,

I need to pass a column as a parameter in my query. I did this:

ALTER PROCEDURE [dbo].[GD_SP_GET_UsersByDIR_COD]

@.Direccao nvarchar(11),

@.prmFieldName nvarchar(25),

@.prmFieldValue nvarchar(25)

AS

BEGIN

IF @.prmFieldValue='*' OR @.prmFieldValue=''

BEGIN

SELECT TOP (100) PERCENT dbo.ADServico_User.UserID, dbo.ADUser.UserName AS Nome, dbo.HARDWARE.New_Computername AS Computername,

dbo.ModeloPC.MOD_ModeloPC AS ModeloPC, dbo.Monitor.MON_Monitor AS Monitor, dbo.Status.StatusNome AS Status,

dbo.HARDWARE.Migrada AS Interven??o, dbo.HARDWARE.MigraViaChange AS [Migrada via Change], dbo.HARDWARE.StatusID,

dbo.HARDWARE.NS_ID

FROM dbo.ModeloPC INNER JOIN

dbo.SERVICO INNER JOIN

dbo.ADServico_User ON dbo.SERVICO.S_GrupoServico = dbo.ADServico_User.GrupoServico INNER JOIN

dbo.HARDWARE ON dbo.ADServico_User.UserID = dbo.HARDWARE.UserID INNER JOIN

dbo.Status ON dbo.HARDWARE.StatusID = dbo.Status.ID ON dbo.ModeloPC.MODELO_ID = dbo.HARDWARE.MODELO_ID INNER JOIN

dbo.Monitor ON dbo.HARDWARE.MONITOR_ID = dbo.Monitor.MONITOR_ID INNER JOIN

dbo.DIRECCAO ON dbo.SERVICO.S_NomeDir = dbo.DIRECCAO.DIR_COD LEFT OUTER JOIN

dbo.ADUser ON dbo.HARDWARE.UserID = dbo.ADUser.UserID

WHERE (dbo.HARDWARE.StatusID <> 6) AND (dbo.DIRECCAO.DIR_COD = @.Direccao)

ORDER BY Nome

END

ELSE

BEGIN

DECLARE @.SQL varchar(7000)

SET @.Direccao='CGD-DAS'

SET @.prmFieldName='UserID'

SET @.prmFieldValue='C095122'

SET @.SQL = 'SELECT

TOP (100) PERCENT dbo.ADServico_User.UserID, dbo.ADUser.UserName AS Nome, dbo.HARDWARE.New_Computername AS Computername,

dbo.ModeloPC.MOD_ModeloPC AS ModeloPC, dbo.Monitor.MON_Monitor AS Monitor, dbo.Status.StatusNome AS Status,

dbo.HARDWARE.Migrada AS Interven??o, dbo.HARDWARE.MigraViaChange AS [Migrada via Change], dbo.HARDWARE.StatusID,

dbo.HARDWARE.NS_ID

FROM dbo.ModeloPC INNER JOIN

dbo.SERVICO INNER JOIN

dbo.ADServico_User ON dbo.SERVICO.S_GrupoServico = dbo.ADServico_User.GrupoServico INNER JOIN

dbo.HARDWARE ON dbo.ADServico_User.UserID = dbo.HARDWARE.UserID INNER JOIN

dbo.Status ON dbo.HARDWARE.StatusID = dbo.Status.ID ON dbo.ModeloPC.MODELO_ID = dbo.HARDWARE.MODELO_ID INNER JOIN

dbo.Monitor ON dbo.HARDWARE.MONITOR_ID = dbo.Monitor.MONITOR_ID INNER JOIN

dbo.DIRECCAO ON dbo.SERVICO.S_NomeDir = dbo.DIRECCAO.DIR_COD LEFT OUTER JOIN

dbo.ADUser ON dbo.HARDWARE.UserID = dbo.ADUser.UserID

WHERE (dbo.HARDWARE.StatusID <> 6) AND (dbo.DIRECCAO.DIR_COD = @.Direccao)

AND '+ @.prmFieldName +' =' + @.prmFieldValue + ' '

EXEC @.SQL

END

END

THE COMAND EXECUTE SUCESSFULLY IN QUERY ANALISER, BUT IN ASP.NET 2.0 CLIENT RETURNS THE FOLLOWING ERROR:

Server Error in '/WS_GestaoDesktop' Application.

---

The name 'SELECT
TOP (100) PERCENT dbo.ADServico_User.UserID, dbo.ADUser.UserName AS Nome, dbo.HARDWARE.New_Computername AS Computername,
dbo.ModeloPC.MOD_ModeloPC AS ModeloPC, dbo.Monitor.MON_Monitor AS Monitor, dbo.Status.StatusNome AS Status,
dbo.HARDWARE.Migrada AS Interven??o, dbo.HARDWARE.MigraViaChange AS [Migrada via Change], dbo.HARDWARE.StatusID,
dbo.HARDWARE.NS_ID
FROM dbo.ModeloPC INNER JOIN
dbo.SERVICO INNER JOIN
dbo.ADServico_User ON dbo.SERVICO.S_GrupoServico = dbo.ADServico_User.GrupoServico INNER JOIN
dbo.HARDWARE ON dbo.ADServico_User.UserID = dbo.H' is not a valid identifier.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: The name 'SELECT
TOP (100) PERCENT dbo.ADServico_User.UserID, dbo.ADUser.UserName AS Nome, dbo.HARDWARE.New_Computername AS Computername,
dbo.ModeloPC.MOD_ModeloPC AS ModeloPC, dbo.Monitor.MON_Monitor AS Monitor, dbo.Status.StatusNome AS Status,
dbo.HARDWARE.Migrada AS Interven??o, dbo.HARDWARE.MigraViaChange AS [Migrada via Change], dbo.HARDWARE.StatusID,
dbo.HARDWARE.NS_ID
FROM dbo.ModeloPC INNER JOIN
dbo.SERVICO INNER JOIN
dbo.ADServico_User ON dbo.SERVICO.S_GrupoServico = dbo.ADServico_User.GrupoServico INNER JOIN
dbo.HARDWARE ON dbo.ADServico_User.UserID = dbo.H' is not a valid identifier.

Source Error:

Line 3269: }

Line 3270: dsHardware.dtGD_SP_GET_UsersByDIR_CODDataTable dataTable = new dsHardware.dtGD_SP_GET_UsersByDIR_CODDataTable();

Line 3271: this.Adapter.Fill(dataTable);

Line 3272: return dataTable;

Line 3273: }

The problem is based here:

WHERE (dbo.HARDWARE.StatusID <> 6) AND (dbo.DIRECCAO.DIR_COD = @.Direccao)

if you want to pass a static value use

WHERE (dbo.HARDWARE.StatusID <> 6) AND (dbo.DIRECCAO.DIR_COD = ''' + @.Direccao + ''')

if you want to pass a identifier use

WHERE (dbo.HARDWARE.StatusID <> 6) AND (dbo.DIRECCAO.DIR_COD = ' + @.Direccao + ')

HTh, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Dear Friend,I changed as you told, but the error still there! :-(

CREATE PROCEDURE [dbo].[GD_SP_GET_UsersByDIR_COD]

@.Direccao nvarchar(11),

@.prmFieldName nvarchar(25),

@.prmFieldValue nvarchar(25)

AS

BEGIN

IF @.prmFieldValue='*' OR @.prmFieldValue=''

BEGIN

SELECT TOP (100) PERCENT dbo.ADServico_User.UserID, dbo.ADUser.UserName AS Nome, dbo.HARDWARE.New_Computername AS Computername,

dbo.ModeloPC.MOD_ModeloPC AS ModeloPC, dbo.Monitor.MON_Monitor AS Monitor, dbo.Status.StatusNome AS Status,

dbo.HARDWARE.Migrada AS Interven??o, dbo.HARDWARE.MigraViaChange AS [Migrada via Change], dbo.HARDWARE.StatusID,

dbo.HARDWARE.NS_ID

FROM dbo.ModeloPC INNER JOIN

dbo.SERVICO INNER JOIN

dbo.ADServico_User ON dbo.SERVICO.S_GrupoServico = dbo.ADServico_User.GrupoServico INNER JOIN

dbo.HARDWARE ON dbo.ADServico_User.UserID = dbo.HARDWARE.UserID INNER JOIN

dbo.Status ON dbo.HARDWARE.StatusID = dbo.Status.ID ON dbo.ModeloPC.MODELO_ID = dbo.HARDWARE.MODELO_ID INNER JOIN

dbo.Monitor ON dbo.HARDWARE.MONITOR_ID = dbo.Monitor.MONITOR_ID INNER JOIN

dbo.DIRECCAO ON dbo.SERVICO.S_NomeDir = dbo.DIRECCAO.DIR_COD LEFT OUTER JOIN

dbo.ADUser ON dbo.HARDWARE.UserID = dbo.ADUser.UserID

WHERE (dbo.HARDWARE.StatusID <> 6) AND (dbo.DIRECCAO.DIR_COD = @.Direccao)

ORDER BY Nome

END

ELSE

BEGIN

DECLARE @.SQL varchar(7000)

SET @.Direccao='CGD-DAS'

SET @.prmFieldName='UserID'

SET @.prmFieldValue='C095122'

SET @.SQL = 'SELECT

TOP (100) PERCENT dbo.ADServico_User.UserID, dbo.ADUser.UserName, dbo.HARDWARE.New_Computername,

dbo.ModeloPC.MOD_ModeloPC, dbo.Monitor.MON_Monitor AS Monitor, dbo.Status.StatusNome,

dbo.HARDWARE.Migrada, dbo.HARDWARE.MigraViaChange, dbo.HARDWARE.StatusID,

dbo.HARDWARE.NS_ID

FROM dbo.ModeloPC INNER JOIN

dbo.SERVICO INNER JOIN

dbo.ADServico_User ON dbo.SERVICO.S_GrupoServico = dbo.ADServico_User.GrupoServico INNER JOIN

dbo.HARDWARE ON dbo.ADServico_User.UserID = dbo.HARDWARE.UserID INNER JOIN

dbo.Status ON dbo.HARDWARE.StatusID = dbo.Status.ID ON dbo.ModeloPC.MODELO_ID = dbo.HARDWARE.MODELO_ID INNER JOIN

dbo.Monitor ON dbo.HARDWARE.MONITOR_ID = dbo.Monitor.MONITOR_ID INNER JOIN

dbo.DIRECCAO ON dbo.SERVICO.S_NomeDir = dbo.DIRECCAO.DIR_COD LEFT OUTER JOIN

dbo.ADUser ON dbo.HARDWARE.UserID = dbo.ADUser.UserID

WHERE (dbo.HARDWARE.StatusID <> 6) AND (dbo.DIRECCAO.DIR_COD ='+ @.Direccao +')

AND '+ @.prmFieldName +' =' + @.prmFieldValue + ' '

EXEC @.SQL

END

END

ERROR:

Msg 203, Level 16, State 2, Procedure GD_SP_GET_UsersByDIR_COD, Line 49

The name 'SELECT

TOP (100) PERCENT dbo.ADServico_User.UserID, dbo.ADUser.UserName AS Nome, dbo.HARDWARE.New_Computername AS Computername,

dbo.ModeloPC.MOD_ModeloPC AS ModeloPC, dbo.Monitor.MON_Monitor AS Monitor, dbo.Status.StatusNome AS Status,

dbo.HARDWARE.Migrada AS Interven??o, dbo.HARDWARE.MigraViaChange AS [Migrada via Change], dbo.HARDWARE.StatusID,

dbo.HARDWARE.NS_ID

FROM dbo.ModeloPC INNER JOIN

dbo.SERVICO INNER JOIN

dbo.ADServico_User ON dbo.SERVICO.S_GrupoServico = dbo.ADServico_User.GrupoServico INNER JOIN

dbo.HARDWARE ON dbo.ADServico_User.UserID = dbo.H' is not a valid identifier.

|||

change the following things..

CREATE PROCEDURE [dbo].[GD_SP_GET_UsersByDIR_COD]

@.Direccao nvarchar(11),

@.prmFieldName nvarchar(25),

@.prmFieldValue nvarchar(25)

AS

BEGIN

IF @.prmFieldValue='*' OR @.prmFieldValue=''

BEGIN

SELECT TOP (100) PERCENT dbo.ADServico_User.UserID, dbo.ADUser.UserName AS Nome, dbo.HARDWARE.New_Computername AS Computername,

dbo.ModeloPC.MOD_ModeloPC AS ModeloPC, dbo.Monitor.MON_Monitor AS Monitor, dbo.Status.StatusNome AS Status,

dbo.HARDWARE.Migrada AS Interven??o, dbo.HARDWARE.MigraViaChange AS [Migrada via Change], dbo.HARDWARE.StatusID,

dbo.HARDWARE.NS_ID

FROM dbo.ModeloPC INNER JOIN

dbo.SERVICO INNER JOIN

dbo.ADServico_User ON dbo.SERVICO.S_GrupoServico = dbo.ADServico_User.GrupoServico INNER JOIN

dbo.HARDWARE ON dbo.ADServico_User.UserID = dbo.HARDWARE.UserID INNER JOIN

dbo.Status ON dbo.HARDWARE.StatusID = dbo.Status.ID ON dbo.ModeloPC.MODELO_ID = dbo.HARDWARE.MODELO_ID INNER JOIN

dbo.Monitor ON dbo.HARDWARE.MONITOR_ID = dbo.Monitor.MONITOR_ID INNER JOIN

dbo.DIRECCAO ON dbo.SERVICO.S_NomeDir = dbo.DIRECCAO.DIR_COD LEFT OUTER JOIN

dbo.ADUser ON dbo.HARDWARE.UserID = dbo.ADUser.UserID

WHERE (dbo.HARDWARE.StatusID <> 6) AND (dbo.DIRECCAO.DIR_COD = @.Direccao)

ORDER BY Nome

END

ELSE

BEGIN

DECLARE @.SQL varchar(7000)

SET @.Direccao='CGD-DAS'

SET @.prmFieldName='UserID'

SET @.prmFieldValue='C095122'

SET @.SQL = 'SELECT

TOP (100) PERCENT dbo.ADServico_User.UserID, dbo.ADUser.UserName, dbo.HARDWARE.New_Computername,

dbo.ModeloPC.MOD_ModeloPC, dbo.Monitor.MON_Monitor AS Monitor, dbo.Status.StatusNome,

dbo.HARDWARE.Migrada, dbo.HARDWARE.MigraViaChange, dbo.HARDWARE.StatusID,

dbo.HARDWARE.NS_ID

FROM dbo.ModeloPC INNER JOIN

dbo.SERVICO INNER JOIN

dbo.ADServico_User ON dbo.SERVICO.S_GrupoServico = dbo.ADServico_User.GrupoServico INNER JOIN

dbo.HARDWARE ON dbo.ADServico_User.UserID = dbo.HARDWARE.UserID INNER JOIN

dbo.Status ON dbo.HARDWARE.StatusID = dbo.Status.ID ON dbo.ModeloPC.MODELO_ID = dbo.HARDWARE.MODELO_ID INNER JOIN

dbo.Monitor ON dbo.HARDWARE.MONITOR_ID = dbo.Monitor.MONITOR_ID INNER JOIN

dbo.DIRECCAO ON dbo.SERVICO.S_NomeDir = dbo.DIRECCAO.DIR_COD LEFT OUTER JOIN

dbo.ADUser ON dbo.HARDWARE.UserID = dbo.ADUser.UserID

WHERE (dbo.HARDWARE.StatusID <> 6) AND (dbo.DIRECCAO.DIR_COD ='''+ @.Direccao +''')

AND '+ @.prmFieldName +' =''' + @.prmFieldValue + ''' '

EXEC @.SQL

END

END

|||

Manid,

I changed as you told, but the error still there! :-(

Regards.

|||I guess you are either trying to change another procedure than you are executing or you will have to provide the whole snippet of code via mail or something to make it reproducable.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Dear Jens,

I created a more simple query, based in the same problem:

CREATE PROCEDURE TEMP

AS

DECLARE @.SQL varchar(8000)

DECLARE @.prmFieldName varchar(20)

DECLARE @.prmFieldValue varchar(20)

SET @.prmFieldName='UserID'

SET @.prmFieldValue='C095122'

SET @.SQL = 'SELECT New_Computername

FROM HARDWARE

WHERE '+ @.prmFieldName +' =''' + @.prmFieldValue + ''' '

EXEC @.SQL

The logical is the some of other queries, but in this one I can't executed it, because returns the following error:

Msg 2812, Level 16, State 62, Procedure TEMP, Line 15

Could not find stored procedure 'SELECT New_Computername

FROM HARDWARE

WHERE UserID ='C095122' '.

(1 row(s) affected)

If you can put this query with column parameter workink good, my problem probably is resolved for the other queries.

Thanks!

|||Sorry and blame on me for not seeing this, you will have to wriite the exec as follows:

EXEC(@.SQL)

HTH, jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Dear friends,

I created a new simple query to try to resolve the problem of passing a column as parameter, using sp_executesql, but still return error. :-(

ALTER PROCEDURE TEMP

@.prmFieldName nvarchar(25),

@.prmFieldValue nvarchar(25)

AS

DECLARE @.SQL varchar(8000)

SET @.prmFieldName='UserID'

SET @.prmFieldValue='C095122'

SET @.SQL = N'SELECT New_Computername

FROM HARDWARE

WHERE '+ @.prmFieldName +' =''' + @.prmFieldValue + ''' '

EXECUTE sp_executesql @.SQL;

ERROR:

Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1

Procedure expects parameter '@.statement' of type 'ntext/nchar/nvarchar'.

|||

Dear Friends,

I found the solution for my problem.

I must use EXECTUTE sp_executesql @.SQL in spite of EXEC @.SQL, and I must use nvarchar(4000) in spite of varchar(8000).

The final Result:

ALTER PROCEDURE [dbo].[GD_SP_GET_UsersByDIR_COD]

@.Direccao nvarchar(11),

@.prmFieldName nvarchar(30),

@.prmFieldValue nvarchar(25)

AS

BEGIN

IF @.prmFieldValue='*' OR @.prmFieldValue=''

BEGIN

SELECT TOP (100) PERCENT dbo.ADServico_User.UserID, dbo.ADUser.UserName AS Nome, dbo.HARDWARE.New_Computername AS Computername,

dbo.ModeloPC.MOD_ModeloPC AS ModeloPC, dbo.Monitor.MON_Monitor AS Monitor, dbo.Status.StatusNome AS Status,

dbo.HARDWARE.Migrada AS Interven??o, dbo.HARDWARE.MigraViaChange AS [Migrada via Change], dbo.HARDWARE.StatusID,

dbo.HARDWARE.NS_ID

FROM dbo.ModeloPC INNER JOIN

dbo.SERVICO INNER JOIN

dbo.ADServico_User ON dbo.SERVICO.S_GrupoServico = dbo.ADServico_User.GrupoServico INNER JOIN

dbo.HARDWARE ON dbo.ADServico_User.UserID = dbo.HARDWARE.UserID INNER JOIN

dbo.Status ON dbo.HARDWARE.StatusID = dbo.Status.ID ON dbo.ModeloPC.MODELO_ID = dbo.HARDWARE.MODELO_ID INNER JOIN

dbo.Monitor ON dbo.HARDWARE.MONITOR_ID = dbo.Monitor.MONITOR_ID INNER JOIN

dbo.DIRECCAO ON dbo.SERVICO.S_NomeDir = dbo.DIRECCAO.DIR_COD LEFT OUTER JOIN

dbo.ADUser ON dbo.HARDWARE.UserID = dbo.ADUser.UserID

WHERE (dbo.HARDWARE.StatusID <> 6) AND (dbo.DIRECCAO.DIR_COD = @.Direccao)

ORDER BY Nome

END

ELSE

BEGIN

DECLARE @.SQL nvarchar(4000)

SET @.SQL = N'SELECT

TOP (100) PERCENT dbo.ADServico_User.UserID, dbo.ADUser.UserName AS Nome, dbo.HARDWARE.New_Computername AS Computername,

dbo.ModeloPC.MOD_ModeloPC AS ModeloPC, dbo.Monitor.MON_Monitor AS Monitor, dbo.Status.StatusNome AS Status,

dbo.HARDWARE.Migrada AS Interven??o, dbo.HARDWARE.MigraViaChange AS [Migrada via Change], dbo.HARDWARE.StatusID,

dbo.HARDWARE.NS_ID

FROM dbo.ModeloPC INNER JOIN

dbo.SERVICO INNER JOIN

dbo.ADServico_User ON dbo.SERVICO.S_GrupoServico = dbo.ADServico_User.GrupoServico INNER JOIN

dbo.HARDWARE ON dbo.ADServico_User.UserID = dbo.HARDWARE.UserID INNER JOIN

dbo.Status ON dbo.HARDWARE.StatusID = dbo.Status.ID ON dbo.ModeloPC.MODELO_ID = dbo.HARDWARE.MODELO_ID INNER JOIN

dbo.Monitor ON dbo.HARDWARE.MONITOR_ID = dbo.Monitor.MONITOR_ID INNER JOIN

dbo.DIRECCAO ON dbo.SERVICO.S_NomeDir = dbo.DIRECCAO.DIR_COD LEFT OUTER JOIN

dbo.ADUser ON dbo.HARDWARE.UserID = dbo.ADUser.UserID

WHERE (dbo.HARDWARE.StatusID <> 6) AND (dbo.DIRECCAO.DIR_COD ='''+ @.Direccao +''')

AND '+ @.prmFieldName +' =''' + @.prmFieldValue + ''' '

EXECUTE sp_executesql @.SQL

END

END

THANKS FOR ALL YOUR IMPORTANT SUPPORT!!!